Moving dynamic group of rows from multiple worksheets to main worksheet

NIZAMPAR

New Member
Joined
Sep 24, 2014
Messages
3
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:
Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

NIZAMPAR

New Member
Joined
Sep 24, 2014
Messages
3
Hi guys,

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

Nikki
 

Watch MrExcel Video

Forum statistics

Threads
1,108,789
Messages
5,524,885
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top