Copy data from different sheets and Paste in master sheet.

[Babin Das]

Hi

I am trying to take all of data from different sheets to master sheet (Append). I am very beginner to Excel VBA. I have written the following code but it does't loop through all the sheets. Can anyone help me out.

Thanks in advance

Sub Copypastemaster()
Dim Lrow As Long, MLR As Long, ws As Worksheet
MasterLR = Sheets("Append").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'ws =1 to Sheets.Count - 1
'Lrow = ws.Cells(Rows.Count, 1).End(xlDown).Row

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Append" Then
    Lrow = ws.Cells(Rows.Count, 1).End(xlDown).Row
        'If MLR = 0 Then
       ws.Range(ws.Cells(2, 1), ws.Cells(Lrow, 5)).Copy
        'Selection.Copy
        'Sheets("Append").Cells(MasterLR, 1).Select
        Sheets("Append").Range("A" & MasterLR).PasteSpecial xlPasteValues
        'Else
        'ws.Range("B3:F" & lrow).Copy Destination:=MasterWs.Range("B" & MLR)
        End If
    'MLR = (Append.Range("B2").Rows.End(xlDown).Row) + 1
    'End If
Next ws
End Sub

 

[Deepak]

Try this..

Sub Copypastemaster2()
Dim Lrow As Long, MLR As Long, ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Append" Then
        With ws
            Lrow = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range(.Cells(2, 1), .Cells(Lrow, 5)).Copy
        End With
        With Sheets("Append")
            .Range("A" & Application.CountA(.Columns(1)) + 1).PasteSpecial xlPasteValues
        End With
      End If
Application.CutCopyMode = False
Next ws
Application.ScreenUpdating = True

End Sub

 

[Deepak]

Alternative optimized code!

Sub Copypastemaster3()
Dim Lrow As Long, MLR As Long, ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Append" Then
        ws.Range("A1").CurrentRegion.Offset(1).Resize(, 5).Copy
       
            With Sheets("Append").Range("A1").CurrentRegion.Resize(, 1)
                If .Rows.Count > 1 Then
                    .End(xlDown).Offset(1).PasteSpecial xlPasteValues
                Else
                    .End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End If
            End With
           
    End If
Application.CutCopyMode = False
Next ws

Application.ScreenUpdating = True
End Sub

 

[Babin Das]

Hi Deepak,

Thanks a lot.

 

[Sameer.k21]

I have a folder with 9 workbooks so far, will increase. Each workbook has multiple sheets in it. Another workbook which is "Consolidated List" of all that which is in 9 workbooks and multiple sheets in it.

Can this be used, if the master sheet ("Consolidated List") is in different workbook?

 

[Deepak]

I have a folder with 9 workbooks so far, will increase. Each workbook has multiple sheets in it. Another workbook which is "Consolidated List" of all that which is in 9 workbooks and multiple sheets in it.

Can this be used, if the master sheet ("Consolidated List") is in different workbook?

You can get the lots of result if do the search for the same or pls start the new thread for very own query.