S
simora
I want to loop through a specific set of worksheets, say sheets 1-10 ,
and each sheet has to reference a workbook ( the same workbook ) from a
week ago.
Any help on where my current code gets put into the loop for it to work.
I'm currently referencing each sheet with a macro one by one.
Eventually, this sheet will grow, so this method is not a realistic
option. The last page ( Sheet 12 ) is a Summary Page. I dont want to
update that.
On a different workbook, I need to start at sheet 4 -11 and carry out
the same process. This is a different project than the first.
Here's my code for the first sheet.
Sub SatMacoTest()
'
' SatMacoTest Macro
' Macro recorded 5/28/2005 by Simora
'
'
' Set the open workbook to the variable Var0
Var0 = ActiveWorkbook.Name
' Get user to select which file to reference to...
Attach = Application.GetOpenFilename _
("Excel Files(*.xls),*.xls,Word Files (*.doc),*.doc,All Files (*.*),*.*", _
Title:="Please select last weeks file...")
' Check to Make sure a file was selected
If Attach = "False" Then
MsgBox "No file was selected, the macro will now finish", vbCritical
End
Else
' Else open the selected book and set the name to variable Var
Workbooks.Open Attach
Var = ActiveWorkbook.Name
End If
' Activate the newest version of workbook
Range("A5").Select
Windows(Var).Activate
Range("A5:A82").Select
Selection.Copy
Windows(Var0).Activate
Range("A5").Select
ActiveSheet.Paste
Windows(Var).Activate
Range("F5:H82").Select
Application.CutCopyMode = False
Selection.Copy
Windows(Var0).Activate
Range("F5").Select
ActiveSheet.Paste
Windows(Var).Activate
ActiveWindow.ScrollColumn = 2
Range("R5:R82").Select
Application.CutCopyMode = False
Selection.Copy
Windows(Var0).Activate
Range("D5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
MsgBox "Last week is updated! ."
Exit Sub
End Sub
and each sheet has to reference a workbook ( the same workbook ) from a
week ago.
Any help on where my current code gets put into the loop for it to work.
I'm currently referencing each sheet with a macro one by one.
Eventually, this sheet will grow, so this method is not a realistic
option. The last page ( Sheet 12 ) is a Summary Page. I dont want to
update that.
On a different workbook, I need to start at sheet 4 -11 and carry out
the same process. This is a different project than the first.
Here's my code for the first sheet.
Sub SatMacoTest()
'
' SatMacoTest Macro
' Macro recorded 5/28/2005 by Simora
'
'
' Set the open workbook to the variable Var0
Var0 = ActiveWorkbook.Name
' Get user to select which file to reference to...
Attach = Application.GetOpenFilename _
("Excel Files(*.xls),*.xls,Word Files (*.doc),*.doc,All Files (*.*),*.*", _
Title:="Please select last weeks file...")
' Check to Make sure a file was selected
If Attach = "False" Then
MsgBox "No file was selected, the macro will now finish", vbCritical
End
Else
' Else open the selected book and set the name to variable Var
Workbooks.Open Attach
Var = ActiveWorkbook.Name
End If
' Activate the newest version of workbook
Range("A5").Select
Windows(Var).Activate
Range("A5:A82").Select
Selection.Copy
Windows(Var0).Activate
Range("A5").Select
ActiveSheet.Paste
Windows(Var).Activate
Range("F5:H82").Select
Application.CutCopyMode = False
Selection.Copy
Windows(Var0).Activate
Range("F5").Select
ActiveSheet.Paste
Windows(Var).Activate
ActiveWindow.ScrollColumn = 2
Range("R5:R82").Select
Application.CutCopyMode = False
Selection.Copy
Windows(Var0).Activate
Range("D5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
MsgBox "Last week is updated! ."
Exit Sub
End Sub