Copying header rows and inserting them into all worksheets

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

BEAUTIFUL!!!! Worked like a charm!!!! Thanks so much Ron!!!