Adding columns using Macros

jr71283

New Member
Joined
Feb 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,
My excel VBA experience is limited. I have a sheet that includes a list of columns that can be inserted to another "Main Page " sheet. I am trying to add the columns to the right of the existing columns in the "Main Page" Sheet through a Button. The button code is as following:
(
Sheets("Insert Variable").Select
Range("C1:D24").Select
Selection.Copy
Sheets("Main Page").Select

Columns("C:D").Select
Selection.Insert Shift:=xlToRight

Application.CutCopyMode = False)

It works but adds always adds the columns into the (column C and D) which makes it add it to the left of the existing ones. This messes up the absolute references.
Can you advise how to amend the code to make the insert button add columns to the right of the existing columns ?

Thanks
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,643
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
With Sheets("Main Page")
   Sheets("Insert Variable").Range("C1:D24").Copy .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
End With
 

jr71283

New Member
Joined
Feb 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,
Thanks for the quick response.

So I tested the code you sent out, however it copies into columns B and C in the "Main Page" Sheet, and if I click on the button again, it doesn't add other columns to the right of column C of the "Main Page". Below is the code I added, just to double check if I created the right macro:

Sub insert1()
With Sheets("Main Page")
Sheets("Insert Variable").Range("C1:D24").Copy .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
End With
End Sub


Looking forward to hearing back from you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,643
Office Version
  1. 365
Platform
  1. Windows
Do you have headers in row 1?
 

jr71283

New Member
Joined
Feb 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

row 1 is empty on both sheets
 

jr71283

New Member
Joined
Feb 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,

so I updated it to this :

Sub insert1()
With Sheets("Main Page")
Sheets("Insert Variable").Range("C2:D24").Copy .Cells(2, Columns.Count).End(xlToLeft).Offset(, 1)
End With
End Sub


But still if I click on the button again, it will replace the contents, not add more to the right of the existing one, how is it possible to do that ?

Thanks
 

jr71283

New Member
Joined
Feb 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
it's working perfectly now. Thanks a lot Fluff, that was really helpful .
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,643
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,145
Members
417,010
Latest member
jnuss03

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