Macro that can update a vlookup and itself

[rfinnegan]

Hi All:

This is a two part question about macros and vlookups, but first let me set the stage -
I have an Excel file (excel 2003) with eight tabs. The tabs are named 0808, 0809, 0810, 0811, 0812, 0813, 0814, and weekly (it’s the sum of the other 7). Today (0810) I will fill in yesterday's sales data (0809).
I’m trying to create a macro that will allow me to update one worksheet per day, and then prep itself for the next day. My current macro will open 3 files, copy several vlookups into the appropriate cells of that day’s worksheet, copy the vlookup formulas onto the next day’s tab, then go back and save the prior vlookup results as values so they won’t be changed the next time I run the macro.

For example working in worksheet 0809, it will open the 3 files the vlookups need to capture data from, copy the vlookup formulas from their “waiting row” (row 4 - several different columns) into about 65 rows starting with row 7, move to the 0810 worksheet and put the vlookup into a “waiting row”, go back to 0809, copy and save value for all the cells so they are not updated tomorrow.

The problem is, if I do run this macro tomorrow, it won’t work because I need one of the vlookups to now look in a different place for its data and I need the vlookups to “wait” in the 0811 worksheet, not 0810.

The end goal is to get this to run automatically at 5am, 7 days a week which is why the simple solution of manually changing a couple of things wont accomplish my goal.

That said…

The first part of my question - the following vlookup looks at last year’s sales.

=VLOOKUP(A7,' [I:\Retail\Reports\Sales\2010\Sales 2010.xls]August2010'!$B$3:$M$103,11,FALSE)

It works fine, except tomorrow I will need it to look in the 12th column, not the 11th. The next day it needs to look in the 13th column, etc.

Is there a way to do that as part of a macro?

The second part (also date related)-
Today the worksheet was named 0809, tomorrow's worksheet is named 0810, then 0811, etc.

Is there a way to update the macro to look for a different worksheet each day?

Or is there a better way to do this?

Thanks in advance.

Here’s my current macro

Sub jump()
'
' jump Macro
' open refernece files, and copy vlookup to selected cells
'
'
Workbooks.Open Filename:="I:\Retail\Reports\daily.xls"
ActiveWindow.WindowState = xlMinimized
ChDir "I:\Retail\Reports\Sales\2010"
Workbooks.Open Filename:= _
"I:\Retail\Reports\Sales\2010\Sales 2010.xls"
Workbooks.Open Filename:= _
"I:\Retail\Reports\Sales\2010\Transactions 2010.xls"
ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMinimized
Range("C4:D4").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-6
Range("C7:D72").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.WindowState = xlMaximized
ActiveWindow.SmallScroll Down:=-84
Range("F4").Select
Application.CutCopyMode = False
Selection.Copy
Range("F7:F69").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-72
Range("L4:M4").Select
Application.CutCopyMode = False
Selection.Copy
Range("L10:M72").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("T4").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-78
Range("T7:T69").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("W4").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-78
Range("W7:W69").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.SmallScroll Down:=-84
Range("Y4").Select
Application.CutCopyMode = False
Selection.Copy
Range("Y7:Y69").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AA5").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
Range("C4:Y4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("0810").Select
ActiveWindow.SmallScroll Down:=-12
Range("C4").Select
ActiveSheet.Paste
Sheets("0809").Select
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=-78
Range("A7:Z74").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AA79").Select
End Sub

 

[GlennUK]

The first part of my question - the following vlookup looks at last year’s sales.

=VLOOKUP(A7,' [I:\Retail\Reports\Sales\2010\Sales 2010.xls]August2010'!$B$3:$M$103,11,FALSE)

It works fine, except tomorrow I will need it to look in the 12th column, not the 11th. The next day it needs to look in the 13th column, etc.

Does the column number relate to the tab name in any way? Maybe it's the 09 part of 0809 plus 2???

The second part (also date related)-
Today the worksheet was named 0809, tomorrow's worksheet is named 0810, then 0811, etc.

Is there a way to update the macro to look for a different worksheet each day?

Does the sheet name relate to today's date? Would it be derived from Format(Date,"mmdd") ... maybe?

 

[rfinnegan]

Yes, the column number is related to the date. For example, on Aug 6, I pulled from column 8, on Aug 7, I pulled from column 9. I made an unsuccesful attempt at something similar to what I believe you're suggesting, but couldn't make it work. My attempt was based on referencing a cell (C1) that had " =today()+2 " in it.

I formatted cell (C1) to show just the day (10, 11, 12, etc) but it didn't work. However when I typed in the numbers 10, 11, or 12, the vlookup worked. I think I was close, but needed to extract just the day.

Yes, to your second question. Today (08/11) I'll be plugging yesterday's data into the 0810 worksheet.

I guess the steps I'm missing are how to convert the dates (and extract (?)) into a format I can use, and how to reference them once I get them converted/extracted.

Conceptually I get it, funtionally, not so much. (that should be on a bumper sticker!)

If you can guide me down that path, I'd appreciate it.

Thanks,

 

[GlennUK]

For the first part, use:
=day(today())+2

And for the second part, it'd be like this:

Sheets(Format(Date-1,"mmdd")).Select

... note that many operations allow you to reference the sheet within a command without having to select the sheet at all. Worth experimenting with if you want your code to be faster and more efficient.