Insert Column with macro

[Garrystone]

Hi everybody,

I am trying to insert a new column in sheet 1 with a macro and at the
same time insert a new column in sheet2. I have used the following
code:

Selection.EntireColumn.Insert
Sheets("Sheet2").Select
Selection.EntireColumn.Insert

However, I need the macro to place the column in sheet 2 in the same
location as that in sheet 1. i.e. if I insert a new column after column
F on sheet 1 a new column will be inserted at column F in sheet 2 and
next time I may insert at column B and again need to have sheet 2
automatically insert a new column at this point. Can anyone help
please?

Thank you

Garry

 

[Kevin B]

Open the workbook and do a save as to have a backup copy handy and press Alt
+ F11 to open the VBE (Visual Basic Editor).

Click INSERT on the menu and click Module and either type or copy/paste the
following code:

Sub DoubleInsert()

Dim strCell As String

If ActiveSheet.Name = "Sheet1" Then
strCell = ActiveCell.Address(ReferenceStyle:=xlA1)
Else
Exit Sub
End If

ActiveCell.EntireColumn.Insert
ThisWorkbook.Sheets("Sheet2").Range(strCell).EntireColumn.Insert

End Sub

To run, click TOOLS in the menu, select MACRO/MACROS, select the macro named
DoubleInsert and RUN to execute.

 

[Garrystone]

Thanks Kevin,

exactly what I needed

Garr