Hello,
I am brand new here and working a new job where I need to get something

right. I hope I have come to the right place for help.


I am writing an excel macro where I want to format a new workssheet &
have it add some columns from a previous worksheet. I have written the
parts to format the sheet & to excute the vlookup that retrieves the
sheets. This workbook is a monthly report where the added columns are
information from the previous month.


My issue is this: when I wrote the macro (using the wizard) I told it
to go to the worksheet '0905' for the lookup. What I would like it to
do is to go to the previous month (eg, to run the macro today it should

go to '1005'). Is there someway I can automatically build it in to look

at the worksheet that is right before the open worksheet, or is there a

way for a box to pop up where the user can enter the name of the sheet
from which they want the current sheet to look up?


Thanks for all your help. I have included my macro below.


Sub TST_TERAPPT()
'
' TST_TERAPPT Macro
' Macro recorded 10/12/2005 by ddm
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Sheets("1005").Select
Sheets("1005").Copy Befo=Workbooks("test0905.xls").Sheets(1)
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Columns("D:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:I").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("0905").Select
Columns("A:I").Select
Selection.Copy
Sheets("1005").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 =
"=VLOOKUP('1005'!RC[-6],'0905'!C[-6]:C[2],7)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'0905'!C[-7]:C[1],8)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'0905'!C[-8]:C,9)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G170")
Range("G2:G170").Select
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H170")
Range("H2:H170").Select
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I170")
Range("I2:I170").Select
Sheets("0905").Select
Range("G1:I1").Select
Selection.Copy
Sheets("1005").Select
Range("G1").Select
ActiveSheet.Paste


End Sub
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/12/2005 by ddm
'


'
ActiveCell.FormulaR1C1 =
"=VLOOKUP('1005'!RC[-6],'0905'!C[-6]:C[2],7)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'0905'!C[-7]:C[1],8)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'0905'!C[-8]:C,9)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G170")
Range("G2:G170").Select
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H170")
Range("H2:H170").Select
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I170")
Range("I2:I170").Select
Sheets("0905").Select
Range("G1:I1").Select
Selection.Copy
Sheets("1005").Select
Range("G1").Select
ActiveSheet.Paste
End Sub