| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying header rows and inserting them into all worksheets
I'm sure this will be a simple solution, just after figuring out how to get
the Split Sheet macro working for me, my brain's a little fried right now. I have one long report that prints out to a single worksheet. I used the Split Sheet Macro (a lifesaver) to create a new sheet after every hard page break. The only problem is the header rows (rows 1-6) are only on the original sheet and the first broken out sheet (Sheet2). How can I copy these rows and insert them into the first six rows in all sheets in the workbook? And as a bonus, if I could get them to not recopy into Sheet2 again, that would make life a little easier. Thanks!!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying header rows and inserting them into all worksheets
Hi mattmac
You can try this one with the header info in "Sheet1" Sub test() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then Sheets("sheet1").Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... I'm sure this will be a simple solution, just after figuring out how to get the Split Sheet macro working for me, my brain's a little fried right now. I have one long report that prints out to a single worksheet. I used the Split Sheet Macro (a lifesaver) to create a new sheet after every hard page break. The only problem is the header rows (rows 1-6) are only on the original sheet and the first broken out sheet (Sheet2). How can I copy these rows and insert them into the first six rows in all sheets in the workbook? And as a bonus, if I could get them to not recopy into Sheet2 again, that would make life a little easier. Thanks!!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying header rows and inserting them into all worksheets
Thanks for the reply Ron. Unfortunately no dice. This macro doesn't do
anything in my spreadsheet. I changed the line: "Sheets("sheet1").Range("A1:I6").Copy" to capitalize "Sheet1" in case that was the issue, but that didn't help either. "Ron de Bruin" wrote: Hi mattmac You can try this one with the header info in "Sheet1" Sub test() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then Sheets("sheet1").Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... I'm sure this will be a simple solution, just after figuring out how to get the Split Sheet macro working for me, my brain's a little fried right now. I have one long report that prints out to a single worksheet. I used the Split Sheet Macro (a lifesaver) to create a new sheet after every hard page break. The only problem is the header rows (rows 1-6) are only on the original sheet and the first broken out sheet (Sheet2). How can I copy these rows and insert them into the first six rows in all sheets in the workbook? And as a bonus, if I could get them to not recopy into Sheet2 again, that would make life a little easier. Thanks!!! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying header rows and inserting them into all worksheets
Have you copy the macro in a normal module in the workbook ?
There was a typo in the other macro Do you see a error when you run this macro ? Sub Test2() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = ThisWorkbook.Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then SourceSh.Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... Thanks for the reply Ron. Unfortunately no dice. This macro doesn't do anything in my spreadsheet. I changed the line: "Sheets("sheet1").Range("A1:I6").Copy" to capitalize "Sheet1" in case that was the issue, but that didn't help either. "Ron de Bruin" wrote: Hi mattmac You can try this one with the header info in "Sheet1" Sub test() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then Sheets("sheet1").Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... I'm sure this will be a simple solution, just after figuring out how to get the Split Sheet macro working for me, my brain's a little fried right now. I have one long report that prints out to a single worksheet. I used the Split Sheet Macro (a lifesaver) to create a new sheet after every hard page break. The only problem is the header rows (rows 1-6) are only on the original sheet and the first broken out sheet (Sheet2). How can I copy these rows and insert them into the first six rows in all sheets in the workbook? And as a bonus, if I could get them to not recopy into Sheet2 again, that would make life a little easier. Thanks!!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying header rows and inserting them into all worksheets
I think I figured out what's happening, and I should have mentioned this in
my original post. My apologies. I have the macro saved in a separate workbook, so I can open it run it every time I run this report to a new workbook. This macro is putting the header rows in the sheets in the "Macro" workbook, not the workbook that I'm trying to format. "Ron de Bruin" wrote: Have you copy the macro in a normal module in the workbook ? There was a typo in the other macro Do you see a error when you run this macro ? Sub Test2() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = ThisWorkbook.Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then SourceSh.Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... Thanks for the reply Ron. Unfortunately no dice. This macro doesn't do anything in my spreadsheet. I changed the line: "Sheets("sheet1").Range("A1:I6").Copy" to capitalize "Sheet1" in case that was the issue, but that didn't help either. "Ron de Bruin" wrote: Hi mattmac You can try this one with the header info in "Sheet1" Sub test() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then Sheets("sheet1").Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... I'm sure this will be a simple solution, just after figuring out how to get the Split Sheet macro working for me, my brain's a little fried right now. I have one long report that prints out to a single worksheet. I used the Split Sheet Macro (a lifesaver) to create a new sheet after every hard page break. The only problem is the header rows (rows 1-6) are only on the original sheet and the first broken out sheet (Sheet2). How can I copy these rows and insert them into the first six rows in all sheets in the workbook? And as a bonus, if I could get them to not recopy into Sheet2 again, that would make life a little easier. Thanks!!! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying header rows and inserting them into all worksheets
OK
Then try this Sub Test3() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = ActiveWorkbook.Sheets("Sheet1") For Each sh In ActiveWorkbook.Worksheets If sh.Name < SourceSh.Name Then SourceSh.Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... I think I figured out what's happening, and I should have mentioned this in my original post. My apologies. I have the macro saved in a separate workbook, so I can open it run it every time I run this report to a new workbook. This macro is putting the header rows in the sheets in the "Macro" workbook, not the workbook that I'm trying to format. "Ron de Bruin" wrote: Have you copy the macro in a normal module in the workbook ? There was a typo in the other macro Do you see a error when you run this macro ? Sub Test2() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = ThisWorkbook.Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then SourceSh.Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... Thanks for the reply Ron. Unfortunately no dice. This macro doesn't do anything in my spreadsheet. I changed the line: "Sheets("sheet1").Range("A1:I6").Copy" to capitalize "Sheet1" in case that was the issue, but that didn't help either. "Ron de Bruin" wrote: Hi mattmac You can try this one with the header info in "Sheet1" Sub test() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then Sheets("sheet1").Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... I'm sure this will be a simple solution, just after figuring out how to get the Split Sheet macro working for me, my brain's a little fried right now. I have one long report that prints out to a single worksheet. I used the Split Sheet Macro (a lifesaver) to create a new sheet after every hard page break. The only problem is the header rows (rows 1-6) are only on the original sheet and the first broken out sheet (Sheet2). How can I copy these rows and insert them into the first six rows in all sheets in the workbook? And as a bonus, if I could get them to not recopy into Sheet2 again, that would make life a little easier. Thanks!!! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying header rows and inserting them into all worksheets
BEAUTIFUL!!!! Worked like a charm!!!! Thanks so much Ron!!!
"Ron de Bruin" wrote: OK Then try this Sub Test3() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = ActiveWorkbook.Sheets("Sheet1") For Each sh In ActiveWorkbook.Worksheets If sh.Name < SourceSh.Name Then SourceSh.Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... I think I figured out what's happening, and I should have mentioned this in my original post. My apologies. I have the macro saved in a separate workbook, so I can open it run it every time I run this report to a new workbook. This macro is putting the header rows in the sheets in the "Macro" workbook, not the workbook that I'm trying to format. "Ron de Bruin" wrote: Have you copy the macro in a normal module in the workbook ? There was a typo in the other macro Do you see a error when you run this macro ? Sub Test2() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = ThisWorkbook.Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then SourceSh.Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... Thanks for the reply Ron. Unfortunately no dice. This macro doesn't do anything in my spreadsheet. I changed the line: "Sheets("sheet1").Range("A1:I6").Copy" to capitalize "Sheet1" in case that was the issue, but that didn't help either. "Ron de Bruin" wrote: Hi mattmac You can try this one with the header info in "Sheet1" Sub test() Dim SourceSh As Worksheet Dim sh As Worksheet Set SourceSh = Sheets("Sheet1") For Each sh In ThisWorkbook.Worksheets If sh.Name < SourceSh.Name Then Sheets("sheet1").Range("A1:I6").Copy sh.Range("A1").Insert Shift:=xlDown Application.CutCopyMode = False End If Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "mattmac" wrote in message ... I'm sure this will be a simple solution, just after figuring out how to get the Split Sheet macro working for me, my brain's a little fried right now. I have one long report that prints out to a single worksheet. I used the Split Sheet Macro (a lifesaver) to create a new sheet after every hard page break. The only problem is the header rows (rows 1-6) are only on the original sheet and the first broken out sheet (Sheet2). How can I copy these rows and insert them into the first six rows in all sheets in the workbook? And as a bonus, if I could get them to not recopy into Sheet2 again, that would make life a little easier. Thanks!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying & Inserting Rows w/o Affecting other Rows Etc. | Excel Worksheet Functions | |||
| copying formulas when inserting new rows | Excel Discussion (Misc queries) | |||
| Inserting multiple rows and copying formulas | Excel Discussion (Misc queries) | |||
| Inserting the same header/footer on all worksheets in a workbook | Excel Discussion (Misc queries) | |||
| Automatically copying fomulae when Inserting rows | Excel Discussion (Misc queries) | |||
Linear Mode
