Error when trying to copy & paste with VBA

[PeteWalker]

Hello everyone,

I have 2 workbooks from which I want to copy a range from one workbook to the other. The code I have written is below, however, when I run the procedure I get an error 1004 message? The message says "PasteSpecial method of range class failed". Can someone help me with this please?

Sub CopyDataOld()
Application.ScreenUpdating = False
Dim oldWBcurrentsht As Integer
oldWBcurrentsht = 1
Dim newWBcurrentsht As Integer
newWBcurrentsht = 1
For i = 1 To 53
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("B6:J43").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("B7:J44").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("B81:J118").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("B82:J119").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("B156:J193").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("B157:J194").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("B231:J268").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("B232:J269").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("B306:J343").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("B307:J344").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("B381:J418").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("B382:J419").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("B456:J493").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("B457:J494").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("K6:U43").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("K7:U44").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("K81:U118").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("K82:U119").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("K156:U193").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("K157:U194").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("K231:U268").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("K232:U269").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("K306:U343").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("K307:U344").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("K381:U418").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("K382:U419").PasteSpecial Paste:=xlPasteValues
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
    Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("K456:U493").Copy
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("K457:U494").PasteSpecial Paste:=xlPasteValues
    ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Protect Password:="PASSWORD", DrawingObjects:=True, Contents:=True, Scenarios:=True
    oldWBcurrentsht = oldWBcurrentsht + 1
    newWBcurrentsht = newWBcurrentsht + 1
Next i
Application.ScreenUpdating = True
MsgBox """Copying"" Completed Successfully", vbInformation + vbOKOnly, "Copy Complete"
End Sub

Pete

 

[VoG]

Hi. Maybe just change the order of unprotecting and pasting

Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Unprotect Password:="PASSWORD"
ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Unprotect Password:="PASSWORD"
Workbooks("Old Workbook.xls").Worksheets("Week " & oldWBcurrentsht).Range("B6:J43").Copy
ThisWorkbook.Worksheets("Week " & newWBcurrentsht).Range("B7").PasteSpecial Paste:=xlPasteValues