Excel VBA master sheet copy-paste error 1004

[robilika]

Hello,


I am new to VBA formulas in excel.


I have a workbook with multiple sheets which needs to be copied(only values) in a master sheet in the same workbook. The problem is i get an error on one of my sheets: "Runtime error 1004 : The information cannot be pasted because the Copy area and the paste area are not the same size and shape" . i noticed that this error occurs only if i have only one row in my table that is not blank.






Here is my Formula :

Sub MockImportNewData()






Application.ScreenUpdating = False






Sheets("BLUGI").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False






Sheets("PANT").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.End(xlDown).Select


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False






Sheets("BLUZE").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.End(xlDown).Select


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False






Sheets("PULOVER").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.End(xlDown).Select


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False










Sheets("FUSTE").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.End(xlDown).Select


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False






Sheets("ROCHII").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.End(xlDown).Select


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False






Sheets("GECI").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.End(xlDown).Select


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False






Sheets("GEANTA").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.End(xlDown).Select


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False






Sheets("ACCESORII").Select


Range("A4:G4").Select


Range(Selection, Selection.End(xlDown)).Select


Selection.Copy


Sheets("MASTER").Select


Range("A3").Select


Selection.End(xlDown).Select


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False






Sheets("Master").Select


Range("A5").Select






End Sub

 

[My Aswer Is This]

Edit

 

[My Aswer Is This]

Are you trying to copy from all the sheets in your workbook?
And you mentioned "Tables" are we copying from tables on your sheet?

 

[Trebor76]

Hi robilika,

Welcome to MrExcel!!

You don't have to select each tab to consolidate it. See how this goes:

Option Explicit
Sub Macro1()

    Dim wsMySheet   As Worksheet
    Dim wsConsSheet As Worksheet
    Dim lngLastRow  As Long
    Dim lngPasteRow As Long
    
    Application.ScreenUpdating = False
    
    Set wsConsSheet = Sheets("Master") 'Sheet for the data to be consolidated into. Change to suit.
    
    For Each wsMySheet In ThisWorkbook.Sheets
        If wsMySheet.Name <> wsConsSheet.Name Then
            lngLastRow = wsMySheet.Range("A:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            lngPasteRow = wsConsSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            wsMySheet.Range("A4:G" & lngLastRow).Copy
            wsConsSheet.Range("A" & lngPasteRow).PasteSpecial xlPasteValues
        End If
    Next wsMySheet
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[My Aswer Is This]

If your trying to copy from all sheets into the Sheet named Master:
Try this:

Sub Copy_Ranges()
'Modified  4-2-18 2:05 AM EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrowa = 3
Dim i As Long
    
For i = 1 To Sheets.Count
    If Sheets(i).Name <> "Master" Then
        Lastrow = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
        
        Sheets(i).Range("A4:G" & Lastrow).Copy
        Sheets("Master").Cells(Lastrowa, 1).PasteSpecial Paste:=xlPasteValues
        Lastrowa = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub