Concatenate two columns with Excel Vba in six different tabs

Question

I need to concatenate with VBA the texts contained in two columns in Excel spreadsheet, joining them in another column, but performing this procedure for six tabs.

The data of columns B and H need to be concatenated with a space between them in column A. Adapting the codes I found I was able to perform the procedure to only one tab, but as I don’t have the necessary knowledge of repeating structures, I’m not able to perform in the others. It is worth mentioning that the worksheet contains other tabs besides the six, and in these others I do not intend to concatenate, because it has a different layout than the others, which prevents to perform the procedure by workbook.

The tabs where I wish the concatenation are called so:

SEM_0_EQUIP, SEM_1_EQUIP, SEM_2_EQUIP, SEM_0_LOCAIS, SEM_1_LOCAIS, SEM_2_LOCAIS

The existing and adapted code was thus:

Sub Concatena_dados_de_duas_colunas()

Dim vaColuna2 As Variant, vaColuna8 As Variant, vaDados() As Variant 'variavel para as colunas 2, 8 e dados
Dim wsPlan As Worksheet
Dim rnColuna2 As Range, rnColuna8 As Range, rnDados As Range 'variavel para as colunas 2 e 8
Dim iNumero As Long

Set wsPlan = Worksheets("SEM_0_EQUIP") 'A variavel é igual a planilha desejada
Set rnColuna2 = wsPlan.Range("B3", Range("B1048576").End(xlUp)) 'setada a coluna 2 com o range possivel
Set rnColuna8 = wsPlan.Range("H3", Range("H1048576").End(xlUp)) 'setada a coluna 8 com o range possivel

vaColuna2 = rnColuna2.Value
vaColuna8 = rnColuna8.Value

ReDim vaDados(1 To UBound(vaColuna2))

For iNumero = 1 To UBound(vaColuna2)
vaDados(iNumero) = vaColuna2(iNumero, 1) & " " & vaColuna8(iNumero, 1)
Next iNumero

Set rnDados = wsPlan.Range("A3", Range("A" & UBound(vaColuna2) + 2)) 'o mais dois no final é para fazer com que os dados comecem na linha 3
rnDados.Value = Application.Transpose(vaDados)

vaColuna2 = rnColuna2.Value
vaColuna8 = rnColuna8.Value

ReDim vaDados(1 To UBound(vaColuna2))

For iNumero = 1 To UBound(vaColuna2)
vaDados(iNumero) = vaColuna2(iNumero, 1) & " " & vaColuna8(iNumero, 1)
Next iNumero

Set rnDados = wsPlan.Range("A3", Range("A" & UBound(vaColuna2) + 2)) 'o mais dois no final é para fazer com que os dados comecem na linha 3
rnDados.Value = Application.Transpose(vaDados)

End Sub

I even believe that there is a possibility to improve this code.

Answer 1

How are you?

As you need to fill in a few tabs, the solution can be solved with a "For...each" repeat structure and an "If" clause".

Basically, the code below goes through each tab of the worksheet whose name is stipulated in the clause "If" and then fills column A with the concatenated values of columns B and H of each worksheet, up to the last row filled in column B:

Private Sub btConcatenacao_Click()

Dim Sheet           As Worksheet
Dim A               As Integer
Dim UltCel          As Range

Application.ScreenUpdating = False

'Apaga a coluna A das planilhas
For Each Sheet In ThisWorkbook.Sheets
    
    If Sheet.Name = "SEM_0_EQUIP" Or Sheet.Name = "SEM_1_EQUIP" Or Sheet.Name = "SEM_2_EQUIP" Or Sheet.Name = "SEM_0_LOCAIS" _
       Or Sheet.Name = "SEM_1_LOCAIS" Or Sheet.Name = "SEM_2_LOCAIS" Then
    
        Sheet.Range("A:A").EntireColumn.ClearContents
            
    End If

Next Sheet

'Realiza a rotina de concatenação nas abas desejadas
For Each Sheet In ThisWorkbook.Sheets
    
    If Sheet.Name = "SEM_0_EQUIP" Or Sheet.Name = "SEM_1_EQUIP" Or Sheet.Name = "SEM_2_EQUIP" Or Sheet.Name = "SEM_0_LOCAIS" _
       Or Sheet.Name = "SEM_1_LOCAIS" Or Sheet.Name = "SEM_2_LOCAIS" Then
    
        A = 1
        Set UltCel = Sheet.Range("B1048576").End(xlUp)
            
        Do While A <= UltCel.Row
        
            Sheet.Range("A" & A).Value = Sheet.Range("B" & A).Value & " " & Sheet.Range("H" & A).Value
        
            A = A + 1
        
        Loop
            
    End If

Next Sheet

Application.ScreenUpdating = True

End Sub

The spreadsheet I used was this: https://www.transfernow.net/dl/20210410WYCuKYdG

I hope it helps!