I have a workbook for each store(5 wkbooks). There is a sheet for each period in the year (13) for each store and each sheet has 4 tables of figures on it (one for each week).
Once a period I will be copying and pasting this information to another workbook. That workbook has 4 sheets, one for each week (week one of all the stores goes on one sheet vertically).
This is the way (see below) I am copying and pasting (it's recorded) one week of info for one store. I'm sure there is a more efficient way. Especially since the sheets I'll be copying from are identical (in that the info that goes in B1 on sheet 1 for store 1 is the same info that goes in B1 on sheet 2 for store 2).
The sheets I am copying to, is the one with 4 sheets, one for each week. Sheet 1 has store 1's data for week 1 and under that store 2's data for week 1, etc.
Windows("202forecast.xls"
.Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Application.Goto Reference:="Period1Week1_202"
Selection.Copy
Windows("PeriodXXInProcess.xls"
.Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Application.Goto Reference:="Week1_202"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
I have the same block as above for Period1Week2 and so on.
If this makes sense, does anyone have an idea of what would make this more efficient? Maybe use of variables, or some type of array?
Thank you -
Once a period I will be copying and pasting this information to another workbook. That workbook has 4 sheets, one for each week (week one of all the stores goes on one sheet vertically).
This is the way (see below) I am copying and pasting (it's recorded) one week of info for one store. I'm sure there is a more efficient way. Especially since the sheets I'll be copying from are identical (in that the info that goes in B1 on sheet 1 for store 1 is the same info that goes in B1 on sheet 2 for store 2).
The sheets I am copying to, is the one with 4 sheets, one for each week. Sheet 1 has store 1's data for week 1 and under that store 2's data for week 1, etc.
Windows("202forecast.xls"
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Application.Goto Reference:="Period1Week1_202"
Selection.Copy
Windows("PeriodXXInProcess.xls"
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Application.Goto Reference:="Week1_202"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
I have the same block as above for Period1Week2 and so on.
If this makes sense, does anyone have an idea of what would make this more efficient? Maybe use of variables, or some type of array?
Thank you -