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