Insert column into same spot in multiple worksheets

[HookEm]

Using VBE, how do I insert a new column into multiple worksheets at the same time?

Here's what I tried which did not work:
Sub Macro1()

Sheets.Select
Range("D4").Select
Selection.EntireColumn.Insert
Range("D5").Select
ActiveCell.FormulaR1C1 = "CODE"
End Sub

Thanks.

 

[Barry Katcher]

Assuming you want to insert a column in sheets 1, 2 & 3 and between columns A & B:

Sub InsertColumnSheets1and2and3()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
End Sub

 

[HookEm]

I have to do this to several workbooks created by others. The names of the individual worksheets will be different. Is there a way I can select them all regardless of what they are named?

Sorry, forgot to add this: format will be the same on all worksheets. In other words, I can select cell D4 and insert column on each. Just the name of the worksheet will be different.

Thanks,
HookEm
This message was edited by HookEm on 2002-07-05 12:29

 

[Barry Katcher]

You're correct, as far as I can see; you would have to edit the macro and change all the "Sheet1", etc., to the new names before the macro would run properly. How often do you have to do this? Wouldn't it be simpler just to do it manually? Select all the sheets, right-click on the column header of the column you want to insert in front of, and click on "Insert" Fini.

 

[HookEm]

I'm sure it would be easier than the time and effort that I'll put into making the macro work. Just one of those "There ought to be a way to ..." things!

 

[NateO]

The VBA isn't too bad, the following ought to be variable:<pre>
Sub insrt()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next 'trap for data in "iv"
ws.[d:d].Insert
Next ws
End Sub</pre>

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>

This message was edited by NateO on 2002-07-05 12:54

 

[Barry Katcher]

NateO, yours works beautifully, with the one caveat that it will insert a column in EVERY worksheet. If that's okay, o.k. but if one wanted to insert the column in, say, the first 5 of 7 worksheets, no go.

 

[NateO]

True, I figured for each worksheet. A simple if statement corrects for your caveat:<pre>
Sub insrt()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Index = 6 Then Exit Sub
On Error Resume Next 'trap for data in "iv"
ws.[d:d].Insert
Next ws
End Sub</pre>

One could also use an if command to pick a variable range, say sheets 3 through 5.

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>

This message was edited by NateO on 2002-07-05 13:11

 

[HookEm]

As usual, the best help on the internet. Thanks folks.

HookEm

 

[sampathmeka]

Hello All,

As discussed by nato i have one question , Is there any possibility where ws.[d:d].Insert ,here can "d " be dynamic anything we insert in one sheet should be done in all other sheets.

Thank you ,
Sampath