Eric,
This is exactly what I was looking for! I can't tell you how much less
cumbersome it is than the code I was trying to write. My only issue is I keep
erroring out on this line:
ActiveWorkbook.Sheets("Week " & i).Select
in the updating columns AJ and AK section. "Run-time error '9': Subscript
out of range"
Any ideas?
Thanks sooo much for your help!!
Amy
"EricG" wrote:
How about something like the code below? I assumed that you don't actually
have to copy all the data from one sheet to the next. Instead, I simply
rename Weeks 2 through 9 to Weeks 1 through 8, then copy the new sheet and
name it Week 9. Copy the code below into a general module in your main file,
then run the subroutine "Shift_Weekly_Data". The "Data_9.xls" file must also
be open when you run it.
Option Explicit
'
' Shift_Weekly_Data Macro
' Macro created 2/23/2010
'
Sub Shift_Weekly_Data()
Dim i As Long, nRows As Long
Dim thisWB As String
'
' Turn off screen updating and alerts for now.
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'
' Move sheets "to the left" one week by simply renaming.
' No need to copy and paste a lot of data.
'
thisWB = ActiveWorkbook.Name
ActiveWorkbook.Sheets("Week 1").Delete
ActiveWorkbook.Sheets("Week 2").Name = "Week 1"
ActiveWorkbook.Sheets("Week 3").Name = "Week 2"
ActiveWorkbook.Sheets("Week 4").Name = "Week 3"
ActiveWorkbook.Sheets("Week 5").Name = "Week 4"
ActiveWorkbook.Sheets("Week 6").Name = "Week 5"
ActiveWorkbook.Sheets("Week 7").Name = "Week 6"
ActiveWorkbook.Sheets("Week 8").Name = "Week 7"
ActiveWorkbook.Sheets("Week 9").Name = "Week 8"
'
' Copy the new Week 9 data from the other (open) file.
'
Workbooks("Data_9.xls").Sheets("Sheet1").Copy
After:=Workbooks(thisWB).Sheets("Week 8")
ActiveWorkbook.Sheets("Sheet1").Name = "Week 9"
'
' Now update columns AJ and AK
'
For i = 1 To 9
ActiveWorkbook.Sheets("Week " & i).Select
nRows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
ActiveSheet.Columns("AJ:AK").ClearContents
ActiveSheet.Range("AJ1").FormulaR1C1 = "Week"
ActiveSheet.Range("AK1").FormulaR1C1 = "Sector"
ActiveSheet.Range("AJ2:AJ" & nRows).FormulaR1C1 = ActiveSheet.Name
ActiveSheet.Range("AK2:AK" & nRows).FormulaR1C1 =
"=TRIM(RC[-36])&TRIM(RC[-35])"
Next i
'
' Turn on screen updating and alerts for now.
'
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'
' Done!
'
End Sub
HTH,
Eric
"Amy" wrote:
I'm not sure how auto-filtering would assist my process. I'm trying to
reclassify data, not filter it. And I need to keep it on the separate
worksheets to serve other processes dependant on the data.
Thanks,
Amy
"Don Guillett" wrote:
It seems that just having all on ONE sheet and using datafilterautofilter
would be better.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Amy" wrote in message
...
I have a workbook that has 9 sheets named "Week 1", "Week2"..."Week 9". I
need a macro to do several things in the following order
1. Copy all data on each week to the previous numbered week. i.e. Week 9
data replaces Week 8 data, Week 8 replaces Week 7...Week 1 data is
replaced
by Week 2 data. I usually just select the whole sheet and do a copy/paste
values to the previous sheet.
2. A new sheet of data is imported from another file into the Week 9
sheet.
The file is named "Data_9.xls", "Sheet1" should replace the data in Week
9.
3. Every spreadsheet, Week 1:Week 9 needs a column of data added in column
AJ. The header is "Week". I need this column to fill (in every cell on a
line with data) with the sheet name. i.e. on the sheet named "Week 1" cell
AJ1 would be "Week" and AJ2:AJ[END] would be "Week 1"
4. Every spreadsheet, Week 1:Week 9 needs a column of data added in column
AK. The header is "Sector". The formula for each cell is =trim(A2)&"
"&trim(B2) relative to the row it's on. Again this would autofill all
cells
as long as there is data.
Can anyone help? Got some help with another spreadsheet here and saved a
ton
of time. I love macros! Working on learning to write them myself but in
the
mean time this is so helpful!! Thank you so much in advance!!!
Amy
.