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

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" <(E-Mail Removed)> wrote in message news:A83E3750-7618-4827-98DE-(E-Mail Removed)...
> 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!!!

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" <(E-Mail Removed)> wrote in message news:A83E3750-7618-4827-98DE-(E-Mail Removed)...
> > 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!!!
>

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" <(E-Mail Removed)> wrote in message news:8981AE02-EEE9-4B52-9A95-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:A83E3750-7618-4827-98DE-(E-Mail Removed)...
>> > 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!!!
>>

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" <(E-Mail Removed)> wrote in message news:8981AE02-EEE9-4B52-9A95-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:A83E3750-7618-4827-98DE-(E-Mail Removed)...
> >> > 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!!!
> >>
>

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" <(E-Mail Removed)> wrote in message news:E101060B-6DA2-43A8-8A02-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message news:8981AE02-EEE9-4B52-9A95-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news:A83E3750-7618-4827-98DE-(E-Mail Removed)...
>> >> > 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!!!
>> >>
>>

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" <(E-Mail Removed)> wrote in message news:E101060B-6DA2-43A8-8A02-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message news:8981AE02-EEE9-4B52-9A95-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message news:A83E3750-7618-4827-98DE-(E-Mail Removed)...
> >> >> > 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!!!
> >> >>
> >>
>