Excel 2013 :: VBA Macro For Selecting Current Week Range

May 30, 2013

I currently have this beast of a code running in Excel 2013:

HTML Code:
Sub OBTAIN_RAW_DATA_MACRO()
Dim NextCol As Long

'Copy data
Workbooks.Open Filename:= _

[Code] ........

However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.

Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?

View 4 Replies


ADVERTISEMENT

Excel 2013 :: Disable Automatically Scrolling To The Top Of Current Cell?

Jan 2, 2014

I am using Excel 2013. I have a spreadsheet that has a lot of very tall cells. When I use the scrollbar to scroll to certain parts of the spreadsheet it will automatically scroll up or down to the top of a cell. So if I want to view the middle of two tall cells that take up the whole screen it will automatically scroll up to the top of the highest cell that I'm currently viewing.

I just want to be able to scroll to where I want to scroll without Excel moving me up or down. I've tried to find answers on message boards but no luck.

View 2 Replies View Related

Selecting A Range In Excel Macro

Jan 6, 2009

I have created the below macro to show the user (when he / she clicks on a button) how many days are left till the deadline day (taken from MS Project).

Sub DaysLeft()
Dim rng As Range
Dim count As Integer
Set rng = Sheets("Programme Reporting").[E2:E200]
count = Application.Evaluate("NETWORKDAYS(Today(),E3)")
Range("L3:L200").Select
ActiveCell.Value = count
End Sub

The range I want to output the days to is L3:L200 that seems to be okay. I have an issue with trying and putting a range in the NETWORKDAYS area, if I try and put in E3:E200 (the range of dates) it fails, as does $E$3:$E$200. At the moment the above code does work but it only puts in the days for one of the entries (E3).

View 9 Replies View Related

How To Insert Date Range Automatically Which Is Referring Current Week

Mar 6, 2014

i got a problem with date range.actually i wanna to insert date range automatically which is referring current week.

View 2 Replies View Related

Selecting A Range Of Cells Relative To The Current Cell

Oct 31, 2006

I would like to select a range of cells relative to the current cell and move them to the right two cells. Basically, if I were in cell A1 I want to be able to have a macro select A1 - A8 and move them over by two cells leaving A1 and A2 blank.

Excel 2003

View 9 Replies View Related

Excel 2013 :: Pivot Table Compare Current Data With Refresh Data And Format New Values?

Jun 18, 2014

vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.

So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.

My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.

Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below

[Code] .....

View 1 Replies View Related

Create Macro That Filter Date In A Column From One Week Previous Till Current Date

Mar 14, 2014

I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.

I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.

Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.

I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.

this was the macro that was created

Code:
ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
Range("A59").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"
Range("A60").Select

View 8 Replies View Related

Selecting Data From A Date Range Based On The Current Date

Jun 20, 2013

I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:

09-15 16-22 23-29

with the month manually put in above it.

then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.

Budget Picture.jpg

View 12 Replies View Related

Excel 2013 :: How To Add Date Range

Mar 4, 2014

I have a big spreadsheet with IT / Finance data. Below is the formula, I currently have. I am summing column Y in Tab Savings Data Detail, where column A does not equal Parent, and The Value in Column AK on the Savings data details tab matches the value in current tab for cell B26 and where column G on the savings detail tab equals Transition to IT. I am adding that value to the second sumifs statement essentially the values in column X on the savings detail tab with the same criteria.

Now I need to add to each of these formulas. I need to sum only the values where the date value in column W on the data details tab is greater than or equal to 01/01/2013 and less than or equal to 12/31/2013

find the easiest way?

=SUMIFS('savings data details'!$Y:$Y,'savings data details'!$A:$A,"Parent",'savings data details'!$AK:$AK,$B26,'savings data details'!$G:$G,"Transition to IT")+
SUMIFS('savings data details'!$X:$X,'savings data details'!$A:$A,"Parent",'savings data details'!$AK:$AK,$B26,'savings data details'!$G:$G,"Transition to IT")

View 1 Replies View Related

Excel 2013 :: Get Range Of Results From Input

Aug 31, 2013

Here is an example of what I am trying to do in E2013.

The orange Bin is my input. It want that to return the column associated with the # as show in orange output column. I think this is an index command? I cannot seem to make it work.

Also - is there an easier way to insert sheets other than a screen grab and photos?

View 5 Replies View Related

Excel 2013 :: Conditional Formatting Twice And Over A Range?

Dec 26, 2013

Using Excel 2013.

I have two columns of numbers. For a row, if the right number is greater than the left number, then color it green, if it is less then color it red.

e.g.

A1 = 100 B1 = 101, then B1 gets colored green
A2 = 100 B2 = 99, then B2 gets colored red

etc.

I have clicked on conditional formatting with A2 selected, then chose A1 for values greater than and chose green. I then copied the formula for all rows. I then did the same for values less than and chose red.

It is not coloring the cells correctly. On some rows when B

View 9 Replies View Related

Excel 2013 :: Printing A Range Of Cells?

Jun 11, 2014

I am using Excel 2013

I need to print to a printer that is not the default printer, a range of Cells e.g. B4:L28 on Sheet Print Out.

The code is to be added to a VBA routine that already exists that collects & arranges the data on the Print Out sheet from other sheets in the workbook. This routine is assigned to a button on another sheet.

View 2 Replies View Related

Excel 2013 :: Mirror Range Of Cells In Different Sheets?

Jul 9, 2014

I would like to mirror a range of cells between two sheets in the same workbook, so that if the data is manually input into one of the cells in range (E5:H11) in 'Sheet 1', it is automatically updated in the corresponding cell in (H33:K39) of 'Sheet 2', and vice versa.

Using Office 2013. T

View 6 Replies View Related

Excel 2013 :: Eliminating Blank Cells In Range In Row?

Nov 5, 2012

I am using windows 7 64bit sp1 and excel 2010 32bit sp1

I am trying to fix this formula (it takes a range in a unique column, and multiple row:

Code:
=IFERROR(INDEX($B$1:$B$6,SMALL((IF(LEN($B$1:$B$6),ROW(INDIRECT("1:"&ROWS($B$1:$B$6))))),ROW(A1)),1),"")
from here

I want to make it work on my array in a row, like this: (i need to remove the blank cells and the output have to be still in a row)

value1
empty cell
empty cell
value 2
value 3
empty cell
output:
value1
value2
value3

I need a function becouse I have dynamic table with something like 10k rows and 50 columns.

View 2 Replies View Related

Excel 2013 :: Conditional Format Named Range

Mar 15, 2013

I have a spreadsheet that keeps track of the equipment we have on rent. Whenever we have a new rental, I insert a row and enter the information. I have several conditional formats I am applying - (1) making the font a light grey so that it is hard to see on any rental that has been called off, but invoicing isn't complete on, (2) making the font red on any item that is within 3 days of the term, and highlighting any row that isn't showing an invoice in over 45 days. When anything is completed (rental has been called off, and the final invoice has been approved), I cut the row out and put it in a sheet entitled "Closed Equipment". When I cut out closed records or add new records, it will occasionally mess up my conditional formatting by only referencing one row. Additionally, I don't want the conditional formatting to transfer over to the "Closed Equipment" tab.

Would this be easier to keep clean and straight if I used VBA? Or should I just continue to occasionally check my conditional formatting and clean it up? I thought if I used a Named Range instead of cell references, it might work better, but entering named range "On_Rent" converts to the cell range ($A$4:$AA$194).

I am using Windows 8, Excel 2013. The file is on a network drive, and others in the office can look at it (they all run Windows 7, and either Excel 2010 or 2007), but they look at it so seldom.

View 1 Replies View Related

Excel 2013 :: Find And Replace Values In Selected Range?

Jul 13, 2014

I have Excel 2013. I would like to use the code below but I need it to let me select the range instead of specifying it in the code, then pop up a form into which I can fill in what I want it to search and what I need it replaced with.

[Code] ......

View 3 Replies View Related

Excel 2013 :: Macro To Update New Sheet?

Jun 23, 2014

I've got a spreadsheet that pulls data from a SQL server.

I'm looking to have a button on the first sheet that extracts the data from SQL and inserts it into sheet 2 of spreadsheet. The data connection from SqL is working fine but when I click on the button sheet 2 is blank.

View 1 Replies View Related

Excel 2013 :: Using Macro To Format Table?

May 14, 2014

I am using excel 2013. I have the table look like the above, in the diagram 1, i want to have the macro such that it can be formatted to the 2nd diagram. Can this be done using macro ? [URL]

View 2 Replies View Related

Excel 2013 :: Formula Test If Value Matches Any Item In Array Or Range?

Mar 19, 2014

Using Excel 2013,

It trying to see if the Month() of a date is in a Array / Range

I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12

View 2 Replies View Related

Excel 2003 :: Sum Data Depending On Week Range

Jun 26, 2014

Objective: I have a financial spreadsheet, which i want to bring to my front sheet the sum of data in between 2 week ranges. For example, if I select week 26 and 52 then all the relevant data will be summed into the relevant cell on my front sheet.

Current Technique: I have on my "data" spreadsheet my columns in B1 down listing Week 1-52 and then the cell headings in B2 onwards. I have created a summary table which currently shows the 4 quarters of the year (1-13, etc) and I am using helper columns to pull this data onto my "front sheet", depending what selection is made on the drop down e.g. 1-13, then all summary data will be dragged onto my front sheet.

On the front sheet, in each cell I need information to be displayed I use the following formula;

"=IF(ISERROR(INDEX(Data!$C$56:$BF$60,Data!$BI56,COLUMNS('Front Sheet'!I13:J13)))"

As you can see the quarter data is useful, but a more flexible date range would be more useful to sum data between any selected week.

View 1 Replies View Related

Excel 2013 :: Macro That Can Reference Worksheet It Has Created

Jul 4, 2014

I am completely new to Macro's and VB and the macro below has been generated using the "Record Macro" function in Excel 2013 with a couple of very minor modifications based on some research I have done (hyperlink & input box). My ultimate goal is to make a copy of my "TEMPLATE", which is hidden and the copy could have a variety of names, then create an entry in my "SUMMARY" table that references cells on the newly created sheet. The new entry on the SUMMARY page should be entered in the next available row ... at the moment I need to make sure I have my cursor in the right place before I run the macro. I also want the first cell in the new "SUMMARY" row to create a hyperlink to the newly created worksheet.

The macro does what I need it to do, as long as I name the new sheet "Test", what I would like is for the Macro to recognise the name of the new worksheet and create links to that name. The rows and columns in each new sheet will remain the same, hence the R##C## part will always work.

The "SUMMARY" and "TEMPLATE" worksheet names will not (ever) change.

View 4 Replies View Related

Excel 2013 :: Enabling Undo After Running A Macro?

Mar 18, 2014

A coworker just ran something by me that i'm quite frankly baffled by. After running a macro, the undo function becomes disabled up to the point of when the macro was ran. I've done a quick search through the forums and can't seem to find this question so i thought i'd ask.

Is there an option to toggle somewhere or 3rd party addon that keeps Undo enabled after running a macro? On a similar topic for my own curiosity, what's the reason that it disables?

View 3 Replies View Related

Excel 2013 :: Define Font Colour Within Macro

Apr 3, 2014

Excel 2013

I am trying to define a font colour within a macro When I record the macro, I choose a colour from the font colour-picker in the Home section of the ribbon. The colour I choose is one of the grey shades Everything seems to work ok, and the macro saves with the colour defined as a long number, e.g.

.Bold = False
.Italic = False
.ThemeColor = xlThemeColordark2
TintAndShade = -0.499984740745262

(Note this is the colour number returned by Excel when I chose a grey font) But when I run the macro, instead of a grey font I get a sort of pale apricot

Do I need to re-index something?

Is there a list anywhere of these long numbers and the colours that they represent?

View 2 Replies View Related

Current Week Beginning And End Date

Apr 20, 2009

Is there a snatch of macro that can assign two variables with the date for the beginning and ending date of the current week?

I need
A= 4-19-2009 12:00AM
and
B=4-25-2009 11:59 PM

Based on the current week being week 16, with the variables changing as the weeks progress.

View 9 Replies View Related

Excel 2013 :: Customize Macro Button Added To Toolbar

Dec 3, 2013

Upgraded to Excel 2013. Do i no longer have the ability to customize the look of my macro buttons i add to the toolbar?

View 2 Replies View Related

Excel 2013 :: How To Record Printer Info With Macro Recorder

Dec 6, 2013

Had 2003 now 2013. Trying to get the macro to select different printers for different doccuments. 2013 macro recorder does not record any printer info or path, all printers show the same wording just application print...

How can I get the recorder to acurrately record and diferentiate printers?

View 2 Replies View Related

Excel 2013 :: Can't Find Macro In Inherited File - How To Search In VBA

Apr 8, 2014

So there's a macro "RoundedRectangle4_click" that's assigned to a box on a worksheet. It's a simple macro, I assume. it just takes the user to the 'Main Sheet' tab. I know the name b/c I rightclicked on the box to get the Macro Assigned.

I click on Macros (F8), it's not listed there, but"RoundedRectangle5_click" is, so I click on it, edit it to match the one I'm searching. Nope, nothing. CREATE button is highlighted.

I've gone to VBA (AltF11) to try to find it among the 25+ worksheets, forms & modules - haven't found it yet.

View 4 Replies View Related

Head Count: If It Is Greater Then 7 Then It Would Be 1 For Current Week

Aug 20, 2007

Currently having problems getting correct head count. I have formula that works for rows 6-8 but fails in row9. The should be answers are in rows 17-20.

Conditions used in formula
*Start date > Start FY =0
* Current Week > End Date =0
* End Date < Start FY =0

The date difference is divided by 7 because there are 7 working days in a week. If it is greater then 7 then it would be 1 for current week. I tried zipping the file but I could not shrink it to required size. find on weblink below: http://maxupload.com/E759C9D9

View 4 Replies View Related

Excel 2013 :: Format Date / Time And Macro To Run On Opening Orkbook

Feb 2, 2013

I need to place a date and time in a cell 14/9/2013 6:00 PM

Tried several formats under custom format without success.

Also need addition to following macro to enable it to start on opening of workbook

Code:

Sub Macro1()
Do
PauseTime = 1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.

[Code]...

View 8 Replies View Related

Selecting All Cells Except Current One

Oct 10, 2013

how do I select all cells from say B6 to the one above the active cell, and all the cells below the active cell, ie i want to select all cells in a column except the active cell and the first 5 cells. (in VBA of course).

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved