Forum Discussion

tridi94's avatar
tridi94
Copper Contributor
Feb 13, 2022
Solved

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's avatar
    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

     

     

tridi94's avatar
tridi94
Copper Contributor
Feb 13, 2022

OliverScheurich 

Thanks for replying.

 

Data from A1 of each should go to C column of Main sheet .

 

from C and D of each sheet to D and E of Main sheet.

 

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 13, 2022

tridi94 

Sub Macro1()

Dim i As Integer
Dim j As Integer
Dim z As Integer

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

Worksheets("main").Cells(j, 3).Value = Worksheets(i).Cells(1, 1).Value
Worksheets("main").Cells(z, 4).Value = Worksheets(i).Cells(3, 3).Value
Worksheets("main").Cells(z + 1, 4).Value = Worksheets(i).Cells(4, 3).Value
Worksheets("main").Cells(z, 5).Value = Worksheets(i).Cells(3, 4).Value
Worksheets("main").Cells(z + 1, 5).Value = Worksheets(i).Cells(4, 4).Value

j = j + 4
z = z + 4


Next i
End Sub

 

Maybe with these lines of code.

Resources