Excel VBA - Add new columns with text and formatting to multiple worksheets

Question

I am working on an Excel macro (button) that will add a column into the same spot in multiple worksheets. Also, this column must have a column header that is input through a dialog box.

Each worksheet contains a table formatted the exact same way - the only difference is the worksheet names.

Here is what I have so far:

Sub CommandButton2_Click()

Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet2").Activate
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow

Dim myValue As Variant

myValue = InputBox("Enter Thought Leadership Title", "New Thought    Leadership", "XXXXX")

Sheets(Array("Sheet1", "Sheet2”)).Select
Range("F5").Value = myValue

End Sub

This has gotten me exactly what I want for 'Sheet1' - adds a column (F) and inputs a header name specified through the dialog box into cell F5 while copying the formatting from the column immediately to the right. However, all it does for Sheet2 (and all others, but removed them for brevity) is add in a column without copying formatting from the right or adding the text specified through the dialog box.

Beginner VBA "programmer" and have gotten this far through using the forums here.

Hopefully I was clear - thanks for any input.

You can try to loop trough your Sheet Array `Sheets(Array(0))... And Sheets(Array(1))` If you have more than two Sheets use a Loop, like `For i = 0 To NumberOfWorksheets` — Benno Grimm, Jul 20 '16 at 20:08
This is a *perfect* example of why you should [avoid using `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). I ***highly*** recommend reading through that, and applying it to your macro. If after that, you are stuck/getting errors, let me know. — BruceWayne, Jul 20 '16 at 20:21

score 2 · Answer 1 · answered Jul 20 '16 at 20:25

Just loop through Array("Sheet1", "Sheet2"). Adding the Type:=2 parameter to the InputBox will ensure that myValue will always be a string. If the user cancels the InputBox then myValue = "".

Sub CommandButton2_Click()
    Dim ws
    Dim myValue As Variant

    myValue = Application.InputBox(Prompt:="Enter Thought Leadership Title", Title:="New Thought    Leadership", Default:="XXXXX", Type:=2)

    If myValue <> "" Then
        For Each ws In Array("Sheet1", "Sheet2")
            With ws
                .Columns("F:F").Insert Shift:=xlToRight
                .Range("F5").Value = myValue
            End With
        Next
    End If

End Sub

BruceWayne · Answer 2 · 2016-07-20T21:11:15.803

2

An alternative to @ThomasInzina is this:

Sub commandButton2_Click_Test()
Dim myValue As Variant

For Each Worksheet In ActiveWorkbook.Worksheets
    With Worksheet
        .Range("F:F").EntireColumn.Insert shift:=xlToRight, copyOrigin:=xlFormatFromRightOrBelow
        myValue = InputBox("Enter Thought Leadership Title", "New Thought    Leadership", "XXXXX")

    End With
Next Worksheet
Worksheets("Sheet1").Range("F5").Value = myValue ' I'm not sure where you wanted this,
Worksheets("Sheet2").Range("F5").Value = myvalue ' or what it does, so tweak as necessary.
End Sub

I tried to keep it as similar to your code as possible, while avoiding .Select (as mentioned in my comment below OP).

Note: This will loop through all worksheets in your workbook. You can add a line If worksheet.name = "Sheet1" or worksheet.name = "Sheet2" to only run it on those.

edited Jul 20 '16 at 21:11

answered Jul 20 '16 at 20:28

BruceWayne

22,923
15
65
110

(a) I think the OP wants the same headings on every sheet, so you should move the setting of myValue outside the loop. (b) Until we have quantum computing where a Sheet name can be both "Sheet1" and "Sheet2" at the same time, **every** worksheet name will fail one of the two tests in your suggested If statement. I think it needs to be `If worksheet.name = "Sheet1" or worksheet.name = "Sheet2"`. – YowE3K Jul 20 '16 at 20:47
@YowE3K - Good points! Thanks for those, I'll edit in. (I fixed that off the top of my head, and can't ever recall the best way to do the `if sheet name is ...`, so thanks!) – BruceWayne Jul 20 '16 at 21:09
You have moved the wrong thing out of the loop. You moved the setting of the heading out, but it should be done in the loop, and you left the InputBox in the loop, but it should only be done once. I'll edit your answer. – YowE3K Jul 20 '16 at 21:41

Excel VBA - Add new columns with text and formatting to multiple worksheets

2 Answers2

Linked