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!!!

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!!!

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!!!

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!!!

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!!!

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!!!

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!!!