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. Then, make sure that every Columns and Range call is qualified with ws. Also see this for how to find the last row. — BigBen
– BigBen, Commented Jan 4, 2021 at 17:51

Michael · Accepted Answer · 2021-01-04 18:45:10Z

1

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.

answered Jan 4, 2021 at 18:45

Michael

1476 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BigBen Over a year ago

Another good link: Use Long instead of Integer.

BigBen Over a year ago

You also don't need all those On Error GoTo 0. But, great to see you solve your own question!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related