Perhaps this will help with part of it. Probably best to just copy the
template or last sheet entirely and clear contents of the undesired info. In
any case you need to try to remove most or all of your selections

Sub gotolastmonthsheet()
x = Month(Date) - 1
y = Year(Date)
Sheets(x & Right(y, 2)).Select
End Sub
original
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
example of removing selections
Sheets("0905").Columns("A:I").Copy _
Sheets("1005").Range("A1")


--
Don Guillett
SalesAid Software

"dnatoday" wrote in message
ups.com...
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