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

11392

New Member
Joined
Oct 20, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
This is the Sheet1 and I've renamed it to s1
enter image description here
Then, I created 2 new sheets, name it as s2 and s3. It works.
VBA Code:
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
Code:
' 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.
enter image description here
Desired output on both s2 and s3
enter image description here
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,756
Messages
5,626,675
Members
416,200
Latest member
Pulsar3000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top