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.

 

[vcoolio]

Hello M.A.I.T,

I just keep reading, studying and experimenting with my own ideas. Excel is so full of surprises to say the least. Its just a hobby for me. Keeps the brain active!
Yeah, I know. I need to get a life!

Cheerio,
vcoolio.

 

[My Aswer Is This]

Thanks for that bit of information. I have never used the Fill feature.

@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

 

[MARK858]

Thanks for that bit of information. I have never used the Fill feature.

And I always forget about it when using multiple sheets in VBA (which is another reason it is an advantage posting on forums because there is normally someone like vcoolio there to give a nudge and say "are you really sure you don't want to do it this way")

 

[My Aswer Is This]

Yep. You can learn a lot here.

And I always forget about it when using multiple sheets in VBA (which is another reason it is an advantage posting on forums because there is normally someone like vcoolio there to give a nudge and say "are you really sure you don't want to do it this way")

 

[vcoolio]

Hey Fellas,

I've learnt quite a bit from you two also.

Keep up the good work

Cheerio,
vcoolio.

 

[My Aswer Is This]

Keeps my brain working also. Writing code is the easy part. Understanding what people want is the hardest part.

Hello M.A.I.T,

I just keep reading, studying and experimenting with my own ideas. Excel is so full of surprises to say the least. Its just a hobby for me. Keeps the brain active!
Yeah, I know. I need to get a life!

Cheerio,
vcoolio.