Excel VBA to select header from Sheet1 at A1 to the end of the table, and paste it to another Sheets

[11392]

This is the Sheet1 and I've renamed it to s1

Then, I created 2 new sheets, name it as s2 and s3. It works.

Sub test()

' 1. Create 2 new sheets, name it as "s2" and "s3"

Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Name = "s2"

Sheets.Add After:=ActiveSheet
Sheets("Sheet2").Name = "s3"

Next step was to copy A1:C1 from s1, and paste it to s2 and s3 also at A1:C1

' 2. Attempt to copy and paste doesn't work yet

Range(Range("A1"), Range("A1").End(xlToRight)).Copy
Sheets("s2").Paste
Sheets("s3").Paste

End Sub

Unfortunately, it didn't work. Instead of pasting the content of A1:C1 from s1, it just selected from A1 to the end on sheet s2 and s3.

Desired output on both s2 and s3

 

[Joe4]

I think the issue is assuming that the new sheets will be named "Sheet1" and "Sheet2" when added. If you already have a sheet in the workbook, it will not call the new sheet "Sheet1", even if you have renamed the original one.

Try this:

Sub test()

' 1. Create 2 new sheets, name it as "s2" and "s3"
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "s2"

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "s3"

' 2. Copy and paste s1 to other sheets
Sheets("s1").Rows(1).Copy
Sheets("s2").Paste
Sheets("s3").Paste

End Sub