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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,934
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,129,686
Messages
5,637,827
Members
416,984
Latest member
dee10

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