Runtime error 1004 - Pastespecial failing me

[Derick63]

Hello to all.
My macro goes to each month worksheet copies a range then paste into a master worksheet. In essence, I'm consolidating all 12 months into one sheet. I'm getting a Runtime Error 1004 - "Pastespecial method of range class failed". What am I doing wrong? Thanks in advance guys.
Oh, if you know a shorter/cleaner way to do this I'm open to your suggestions.

Derick

Sub CopyMonthsToMaster()

'Go to month
'Get last used row in colulm A (LR1)
'Copy range "B9:T" & LR1)
'Go to Master Worksheet and get first blank row in colulm A (LR2)
'Paste copied values
'Select cell A1
'Next month in Array

Dim arrSht, i
Dim LR1, LR2 As Long

    Application.ScreenUpdating = False
   
    arrSht = Array("January", "February", "March", "April", "May", "June", "July", _
             "August", "September", "October", "November", "December")
            
    Sheets("Master").Range("A2:T9999").Clear

    For i = LBound(arrSht) To UBound(arrSht)
        Worksheets(arrSht(i)).Select
        With Worksheets(arrSht(i))
            .Unprotect Password:=""
            Application.CutCopyMode = True
            LR1 = Worksheets(arrSht(i)).Range("A" & Rows.Count).End(xlUp).Row
            Worksheets(arrSht(i)).Range("B9:T" & LR1).Copy
            .Protect Password:=""
            LR2 = Sheets("Master").Range("A9999").End(xlUp).Row + 1
            Sheets("Master").Range("A" & LR2).PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Sheets("Master").Range("A1").Select
            Application.CutCopyMode = False
        End With
    Next i
   
    Application.ScreenUpdating = True
       
End Sub

 

[Alex Blakenburg]

Try moving the line below from its current position to just before the End With line.

            .Protect Password:=""

Also move this final Sheets("Master").Range("A1").Select line to after the Next i line and add a sheet activate first.

    Next i
   
    Sheets("Master").Activate
    Sheets("Master").Range("A1").Select
    Application.ScreenUpdating = True
      
End Sub

 

[Derick63]

Thanks Alex. That worked. I see why it didn't work now, The sheet was being protected before it copied and to clean up within the With/End With from unnecessary instructions. Thanks again Alex

 

[Derick63]

Hey Alex, when I ran the macro I got the header row from some blank sheets on the Master Sheet. The data starts on Row 9. How can I do this;

      LR1 = Worksheets(arrSht(i)).Range("A" & Rows.Count).End(xlUp).Row
         If LR1=8 Then 'Header is Row 8 on all 12 sheets
            Go To Next i 'I know this isn't code but I'd like the macro to skip the current sheet if LR1=8
         Else
            'Continue Macro

Thanks. I'm still wrapping my head around the various ways of Looping in VBA but I'm getting there.

 

[Alex Blakenburg]

Good timing. You indicated that you were interested in other suggestions but I thought I should wait to make sure we fixed the intial problem, so I can incorporate this at the same time.

The things I wanted to point out were:-
• In a Dim statement you have to data type "each" item on the same line. Putting a data type at the end only applies to the last item
• Get rid of references to 9999

Sub CopyMonthsToMaster_v02()

    'Go to month
    'Get last used row in colulm A (LR1)
    'Copy range "B9:T" & LR1)
    'Go to Master Worksheet and get first blank row in colulm A (LR2)
    'Paste copied values
    'Select cell A1
    'Next month in Array
   
    Dim arrSht
    Dim mstrLR As Long, mthLR As Long
    Dim mthHdrRow As Long, i As Long
    Dim shtMstr As Worksheet, shtMth As Worksheet


    Application.ScreenUpdating = False
  
    'arrSht = Array("January", "February", "March", "April", "May", "June", "July", _
    '         "August", "September", "October", "November", "December")
    arrSht = Array("January", "February")
    mthHdrRow = 8
   
    Set shtMstr = Worksheets("Master")
    mstrLR = shtMstr.Range("A" & Rows.Count).End(xlUp).Row   ' remove hard coded 9999
    shtMstr.Range("A2:T" & mstrLR).Clear

    For i = LBound(arrSht) To UBound(arrSht)
        Set shtMth = Worksheets(arrSht(i))
        With shtMth
            .Unprotect Password:=""
            'Application.CutCopyMode = True                 ' Not required
            mthLR = .Range("A" & Rows.Count).End(xlUp).Row
           
            ' Check for no data on month sheet
            If mthLR > mthHdrRow Then
                .Range("B9:T" & mthLR).Copy
               
                mstrLR = shtMstr.Range("A" & Rows.Count).End(xlUp).Row + 1 ' remove hard coded 9999
                shtMstr.Range("A" & mstrLR).PasteSpecial Paste:=xlPasteValues, _
                    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If
               
            .Protect Password:=""
        End With
    Next i
   
    shtMstr.Activate
    shtMstr.Range("A1").Select
   
    Application.CutCopyMode = False                         ' Do at the end of the macro
    Application.ScreenUpdating = True
      
End Sub

 

[Derick63]

PERFECT!!!!!!! It Worked!
This makes so much more sense. Thanks for the lesson too. I'm at that level where I can understand what I'm seeing in VBA and can write the code after learning it but at the level where combining what I've learnt is still a little foggy.
Apparently there's a lot of confusion on VBA search results to skip to the next item in a loop when an if is embedded in a For loop.

Derick

 

[Alex Blakenburg]

Apparently there's a lot of confusion on VBA search results to skip to the next item in a loop when an if is embedded in a For loop.

Not I have understood you exactly but the 2 statements shouldn't straddle each other.
The If statement inside a loop should be fully inside the loop ie both the If and the EndIf should be inside the loop.
It the For Next is inside they if statement then both the For and Next should be inside the "specific section' of the if statement ie either fully inside the True condition action or fully inside the Else condition action. You can use the If to exit the loop with an Exit For.

Sub ForInsideIF() ' ----------------------------------- ' For/Next Inside If/EndIf Statement ' ----------------------------------- If x = y Then For i = 1 To LastRow ' Do Stuff when TRUE Next i Else For i = 1 To LastRow ' Do Stuff when FALSE Next i End If End Sub

Sub IFInsideFor() ' ----------------------------------- ' If/EndIf Statement inside For/Next ' ----------------------------------- For i = 1 To LastRow If c = d Then ' do something Else ' alternative > the Else part is optional End If Next i End Sub

Sub IFInsideFor_EarlyExit() ' ----------------------------------- ' If/EndIf Statement inside For/Next ' ----------------------------------- For i = 1 To LastRow If c = d Then ' Exit the loop Exit For End If Next i End Sub