Error when trying to copy & paste with VBA

P

PeteWalker

New Member
Joined
Mar 13, 2011
Messages
1
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?

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Hi. Maybe just change the order of unprotecting and pasting

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Copy row based on cell value and paste as text in another worksheet
Replies
2
Views
919
DH937
D
V
  • Question Question
Copy each sheet to new workbook, paste values and save to specific folder
Replies
7
Views
684
JerrExcel
JerrExcel
I
  • Solved
Copy and Paste from One workbook to another
Replies
3
Views
656
ipon70
I
V
  • Question Question
Copy Range from Multiple Sheets and Paste Link into a Single Sheet
Replies
2
Views
316
vbascientist
V
D
  • Solved
Trying to copy data from a specific sheet in one workbook to another with VBA
Replies
2
Views
159
druck21
D

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top