Adding columns using Macros

[jr71283]

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

 

[Fluff]

Hi & welcome to MrExcel.
How about

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

 

[jr71283]

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]

Do you have headers in row 1?

 

[jr71283]

row 1 is empty on both sheets

 

[jr71283]

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]

it's working perfectly now. Thanks a lot Fluff, that was really helpful .

 

[Fluff]

Glad to help & thanks for the feedback.