Excel VBA For Each Worksheet Loop (Run Same VBA Macro Code On Multiple Sheets)

Question

I have Workbook with 3 Worksheets with different data. And now I'm trying to execute the same code for each of Worksheets (concatenate specific words with data from 1,3 Columns and then transpose Range of 3 cells from rows to Column) The code below doesn't actually do that i need.It simple do correctly for the last sheet and then copy this result to another sheets. But i need the unique result for each sheet. Any help is greatly appreciated!

Option Explicit

Sub forEachWS()
    Dim wb                  As Workbook
    Dim ws                  As Worksheet

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
    ws.Columns("G:J").Clear
    Call execute(ws)
Next
End Sub

Sub execute(ws As Worksheet)
    Dim intLastRow          As Long
    Dim intR                As Long
    Const intStartRow       As Integer = 2
    Dim destRow             As Long

With ws

  intLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

  destRow = 2

  For intR = intStartRow To intLastRow
      .Range("G" & intR) = "ID =" & Cells(intR, 1).Value
      .Range("H" & intR) = "[FROM].[#" & Cells(intR, 3).Value
      .Range("I" & intR) = "END"
      .Range("G" & intR & ":I" & intR).Copy
      .Range("J" & destRow).PasteSpecial Transpose:=True
      destRow = destRow + 3
  Next
End With
End Sub

Chrome63 Chrome63 · Accepted Answer · 2015-07-12T11:36:40

I was surprised, but it works if:

Cells(intR, n).Value

replace on:

Range("Colmn" & intR).Value

Excel VBA For Each Worksheet Loop (Run Same VBA Macro Code On Multiple Sheets)

1 Answers