Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Insert Column with macro
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 Last edited by Garrystone : January 28th 08 at 04:53 PM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert Column with macro
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).Entir eColumn.Insert End Sub To run, click TOOLS in the menu, select MACRO/MACROS, select the macro named DoubleInsert and RUN to execute. -- Kevin Backmann "Garrystone" wrote: 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 -- Garrystone |
#3
|
|||
|
|||
Thanks Kevin,
exactly what I needed Garry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro- Column Insert | Excel Discussion (Misc queries) | |||
Macro- Column Insert | Excel Discussion (Misc queries) | |||
Macro- Column Insert | Excel Discussion (Misc queries) | |||
insert column macro will incorrectly add another value | Excel Discussion (Misc queries) | |||
Trying to insert a column using variable in a Macro | Excel Discussion (Misc queries) |