How to add a concatenated column to each sheet with a VBA macro?

Question

I want to make an Excel VBA macro that adds a column "A" that contains a concatenation of "B" "C" and "D" from row 2 until there is no more data. Then the macro Applies this to every sheet in the workbook. Each sheet has a different column length.

My current code is as follows:

Sub forEachWs()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    Call ConCat(ws)
Next
End Sub

Sub ConCat(ws As Worksheet)
With ws
       Dim lr As Integer
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Columns("A:A").Insert Shift:=xlToRight
Range("A2").Formula = "=CONCATENATE(RC[1],""."",RC[2],""_"",RC[3])"
Range("A2").AutoFill Range("A2:A" & lr)
Columns("A:A").AutoFit
End With
End Sub

This code adds concatenated columns to the first sheet multiple times. What can I do to make this apply to all sheets in the workbook instead of one sheet repeatedly?

Start [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Then, make sure that every `Columns` and `Range` call is qualified with `ws`. Also see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. — BigBen, Jan 04 '21 at 17:51

score 1 · Answer 1 · answered Jan 04 '21 at 18:45

The code that ended up working was this code that I compiled thanks to BigBen

Sub LoopOverEachColumn()
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        ConCat WS
        On Error GoTo 0
    Next WS
    On Error GoTo 0
    
End Sub

Private Sub ConCat(WS As Worksheet)
    Dim lr As Integer
    lr = WS.Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    WS.Columns("A:A").Insert Shift:=xlToRight
    WS.Range("A2").Formula = "=CONCATENATE(RC[1],""."",RC[2],""_"",RC[3])"
    WS.Range("A2").AutoFill WS.Range("A2:A" & lr)
    WS.Columns("A:A").AutoFit
    On Error GoTo 0
End Sub

I had an error pop up on the final worksheet because it was blank, so I threw some on errors in there to get over bumps in case a coworker tries to use it.

Another good link: [Use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, Jan 04 '21 at 18:48
You also don't need all those `On Error GoTo 0`. But, great to see you solve your own question! — BigBen, Jan 04 '21 at 18:49

How to add a concatenated column to each sheet with a VBA macro?

1 Answers1