Streamlining repetitive report

[Amy]

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

 

[Don Guillett]

It seems that just having all on ONE sheet and using data>filter>autofilter
would be better.

 

[Amy]

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

It seems that just having all on ONE sheet and using data>filter>autofilter
would be better.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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

.

 

[EricG]

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

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

It seems that just having all on ONE sheet and using data>filter>autofilter
would be better.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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

.

 

[rumkus]

Week 9 data replaces Week 8 data, Week 8 replaces Week 7.

 

[Amy]

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

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

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

It seems that just having all on ONE sheet and using data>filter>autofilter
would be better.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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

.

 

[EricG]

I'm not sure why you're getting that error and I'm not. Which version of
Excel are you running? I'm using 2003.

Can you tell me the value of "i" when the macro bombs? If you press the
Debug button when the macro crashes, and then hover over the "i" in "For i =
1 to 9", it will tell you the value. Normally that error means that you are
trying to select or activate a sheet that does not exist in the workbook.
When the macro dies, take a look at the workbook - does it have all nine
weekly sheets on it?

You can try to comment out the "Application.ScreenUpdating" and
"Application.DisplayAlerts" lines. You might get some more information that
way.

Eric

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

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

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

:

It seems that just having all on ONE sheet and using data>filter>autofilter
would be better.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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

.

 

[Amy]

Eric,

Thanks for responding! I'm using 2003 as well.

The value of "i" is 9 when it crashes. All of the "week" sheets are in the
spreadsheet but there are also other sheets in the file with varying names.
They are located at the end of workbook. The week sheets are essentially
sheets 1 thru 9. I counted, checked and double checked. The way it's written
I don't understand why it errors either.

What am I missing?? I'll continue to try to troubleshoot. If you have any
other ideas let me know.

I think you rescued me the last time I needed macro help too. Thanks so much
for your help and time!

Amy

I'm not sure why you're getting that error and I'm not. Which version of
Excel are you running? I'm using 2003.

Can you tell me the value of "i" when the macro bombs? If you press the
Debug button when the macro crashes, and then hover over the "i" in "For i =
1 to 9", it will tell you the value. Normally that error means that you are
trying to select or activate a sheet that does not exist in the workbook.
When the macro dies, take a look at the workbook - does it have all nine
weekly sheets on it?

You can try to comment out the "Application.ScreenUpdating" and
"Application.DisplayAlerts" lines. You might get some more information that
way.

Eric

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

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


:

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

:

It seems that just having all on ONE sheet and using data>filter>autofilter
would be better.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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

.

 

[EricG]

Amy,

I have tried all sorts of ways to get my version to bomb, but it just keeps
working! Since the code stops when it's trying to select the "Week 9" sheet,
that tells me that the sheet doesn't exist in the active workbook. The only
thing that I can imagine happening is that either the sheet is not being
copied over from the "Week9.xls" file, or the sheet is not being renamed from
"Sheet1" to "Week 9". You should check for both of those possibilities.

Without having your actual workbook to test, I don't think I can do much
more to help. I hope you can find the problem.

Eric

 

[Amy]

Thanks for all your help, Eric! I figured it out. Silly spaces! I'll probably
call on your expertise again sometime.

Thanks again!

Amy