Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
Week 9 data replaces Week 8 data, Week 8 replaces Week 7.
---------------------------------------------------------------- Just got very curious. Why do you do this ? If not very private. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
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 "Amy" wrote: 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
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 "EricG" wrote: 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 "Amy" wrote: 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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining repetitive report
Thanks for all your help, Eric! I figured it out. Silly spaces! I'll probably
call on your expertise again sometime. Thanks again! Amy "EricG" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Streamlining my code | Excel Programming | |||
Streamlining sort | Excel Discussion (Misc queries) | |||
streamlining | Excel Discussion (Misc queries) | |||
looking for repetitive information in a report | Excel Programming | |||
Streamlining Code | Excel Programming |