Forum Discussion
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...
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
Sub Macro1()
Dim i As Integer
For i = 2 To Worksheets.Count
Worksheets("main").Cells(i, 3).Value = Worksheets(i).Cells(1, 1).Value
Next i
End Sub
Is this what you are looking for? The macro counts the number of worksheets after the "main" sheet and displays the data from cell A1 of the other sheets in column C of the main sheet. Click the button in cell E2 in the attached file to execute to macro.
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.
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 SubMaybe with these lines of code.
