VBA copy header in Sheet1 and paste in subsequent sheets

[dellzy]

Dear Excel Experts,

I've been trying the whole day and kinda give up on this. Am trying to do this automatically. Copy header from first worksheet to all other worksheets. But come to no avail. Below are my series of coding I've tried:-

Do While i <= Worksheets.Count
Sheets(i).Rows(1).EntireRow.Copy
Sheets(i + 1).Range("A1").Paste
i = i + 1
Sheets(i).Rows(1).Paste
Loop

For i = 1 To Worksheets.Count
Sheets(1).Rows(1).EntireRow.Copy
Sheets(i+1).Range("A1").Select
Selection.Paste
Next

For Each Worksheet In ActiveWorkbook.Worksheets
Sheets(1).Rows("1:1").Copy
Sheets(i).Rows("1:1").Paste
i=i+1
Next

I just don't know what is actually wrong with it. Please help. Really need this for 147 worksheets.

 

[My Aswer Is This]

Try this:
Copies Header from Sheet(1) to all other sheets

Sub Copy_Header()
Application.ScreenUpdating = False
Dim i As Long

    For i = 2 To Sheets.Count
        Sheets(1).Rows(1).Copy Destination:=Sheets(i).Rows(1)
    Next
Sheets(1).Cells(1, 1).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[MARK858]

@My Aswer Is This, you shouldn't need the "Application.CutCopyMode = False" line if using Destination.
Try it, you shouldn't get the flashing dashes to say it is copying around Sheets(1).Rows(1).

 

[dellzy]

Oh, thank u thank u thank u! The code works perfect! You're my saviour! Yess! Yeayy!

Try this:
Copies Header from Sheet(1) to all other sheets

Sub Copy_Header()
Application.ScreenUpdating = False
Dim i As Long

    For i = 2 To Sheets.Count
        Sheets(1).Rows(1).Copy Destination:=Sheets(i).Rows(1)
    Next
Sheets(1).Cells(1, 1).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Thanks Mark.

@My Aswer Is This, you shouldn't need the "Application.CutCopyMode = False" line if using Destination.
Try it, you shouldn't get the flashing dashes to say it is copying around Sheets(1).Rows(1).

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Oh, thank u thank u thank u! The code works perfect! You're my saviour! Yess! Yeayy!

 

[vcoolio]

Hello People,

FWIW, a simple one liner could do the job also.

Sub Test()
    Worksheets.FillAcrossSheets Sheet1.[A1:M1]
End Sub

Just change the range to suit.

Cheerio,
vcoolio.

 

[MARK858]

Correction: previous statement was in error. Apologies.
@ vcoolio, thanks for the tip.

 

[My Aswer Is This]

I have never seen this code before. Where do you find all these secrets?

Hello People,

FWIW, a simple one liner could do the job also.

Sub Test()
    Worksheets.FillAcrossSheets Sheet1.[A1:M1]
End Sub

Just change the range to suit.

Cheerio,
vcoolio.

 

[MARK858]

@My Aswer Is This it is not really a secret, you can record yourself doing it manually

Select the sheet tabs you want
Select the range you want copied on the sheet you are copying from
On the Home tab, in the Editing group click the Fill dropdown arrow.
Click Across Worksheets.
Click whatever option you want.

You'll get something like...

    Range("A1:F1").Select
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll

which the code vcoolio posted is a tidied up equivalent of i.e. the above would be

Sub Macro2()
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).FillAcrossSheets Range:=Range("A1:F1"), Type:=xlAll
End Sub