Moving dynamic group of rows from multiple worksheets to main worksheet

[NIZAMPAR]

Hi all!

I am writing a code to help copy a dynamic group of rows (i.e. groups with same starting row and various end rows) from multiple worksheet to a main worksheet titled "Summary". I would like this code to cycle through each of the worksheets (except those titled "Summary") and in each worksheet, search column A for the row containing the last non-blank value, highlight and copy all values in columns A through D from row 8 down to the identified row containing the last non-blank value, then activate the Summary worksheet, search in column A of the Summary worksheet for the row containing the last non-blank value and paste the values in the next blank row.

The code that I am using generates the following error message related to the code row highlighted in yellow:

Runtime error '1001': The information cannot be pasted because the Copy area and the paste area are not the same size and shape.

The code that I am using is as follows:

Sub SummarizeSheets()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim pasteSheet As Worksheet
     
    Application.ScreenUpdating = False
     
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
        
        lRow = Range("A" & Rows.Count).End(xlDown).Row
        ws.Range("A8:D" & lRow).Copy
        
        Set pasteSheet = Worksheets("Summary")
        pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
        
    Next ws
End Sub

Could someone please help me troubleshoot this error? Any help would be greatly appreciated

Nikki

 

[NIZAMPAR]

bump

help please!

 

[NIZAMPAR]

Hi guys,

Can anyone offer a suggestion please? Any assistance would be greatly appreciated

Nikki