Excel Macro for copying data from multiple sheets using for loop

I need to copy over data from different sheets to one sheets in a single workbook .Need help how to do it using for loop. And the sheet count is different. the below mentioned first snippet is the m...

Macros and VBA

OliverScheurich

Feb 13, 2022

tridi94

This code takes into account a different number of rows in the other worksheets. That might be even better suited to your task.

Sub Macro1()

Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim u As Integer
Dim w As Integer
Dim no_of_rows As Integer


Range("C:E").Clear

z = 0
j = 3
For i = 2 To Worksheets.Count

With Worksheets(i)
no_of_rows = .Range("C" & .Rows.Count).End(xlUp).Row - 2
End With


Worksheets("main").Cells(j, 3).Value = Worksheets(i).Cells(1, 1).Value


For w = 3 To 4
For u = 1 To no_of_rows

Worksheets("main").Cells(u + 3 + z, w + 1).Value = Worksheets(i).Cells(u + 2, w).Value

Next u
Next w


j = j + 2 + no_of_rows
z = z + 2 + no_of_rows


Next i
End Sub

Macro1.xlsm26 KB

Sub Macro1() Dim i As Integer Dim j As Integer Dim z As Integer Dim u As Integer Dim w As Integer Dim no_of_rows As Integer Range("C:E").Clear z = 0 j = 3 For i = 2 To Worksheets.Count With Worksheets(i) no_of_rows = .Range("C" & .Rows.Count).End(xlUp).Row - 2 End With Worksheets("main").Cells(j, 3).Value = Worksheets(i).Cells(1, 1).Value For w = 3 To 4 For u = 1 To no_of_rows Worksheets("main").Cells(u + 3 + z, w + 1).Value = Worksheets(i).Cells(u + 2, w).Value Next u Next w j = j + 2 + no_of_rows z = z + 2 + no_of_rows Next i End Sub

Forum Discussion

Excel Macro for copying data from multiple sheets using for loop

Resources