Error 1004 Paste method of Worksheet class failed

Newbie_92

New Member
Joined
Aug 11, 2022
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Hi,
Im getting an error below when running a macro below. It must be related somehow on unprotecting the master sheet as the code worked perfectly without that.
Any ideas how to fix this?

Thanks in advance! :)

1661847479191.png

VBA Code:
Sub CopyData()
Application.ScreenUpdating = False
Dim LastRow As Integer, i As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 15).Value = "Yes" Then
Range(Cells(i, 1), Cells(i, 14)).Select
Selection.Copy
Workbooks.Open Filename:="path...."
Worksheets("Master").Unprotect Password:="password"
Worksheets("Master").Select
erow = Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Worksheets("Master").Cells(erow, 1).Select
Worksheets("Master").Paste
ActiveWorkbook.Save
Worksheets("Master").Protect Password:="password"
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
When you hit "debug", which line of code does it highlight?

Also, it looks like you are looping through a bunch of cells to copy data from one file to another.
As such, it would seem to make more sense to open the workbook once, before the loop, and unprotect it, do all your copying, then re-protect and close it once at the end after finishing with the loop.
You may not even need the loop. You may be able to copy the whole range at once.

Can you show us what your data looks like (both the source and destination sheets) and what exactly you are trying to copy over?
We may be able to help you really simplify your code.
 
Upvote 0
When you hit "debug", which line of code does it highlight?

Also, it looks like you are looping through a bunch of cells to copy data from one file to another.
As such, it would seem to make more sense to open the workbook once, before the loop, and unprotect it, do all your copying, then re-protect and close it once at the end after finishing with the loop.
You may not even need the loop. You may be able to copy the whole range at once.

Can you show us what your data looks like (both the source and destination sheets) and what exactly you are trying to copy over?
We may be able to help you really simplify your code.
Thanks for the fast response! This "Worksheets("Master").Paste" is highlighted when the error occurs. Below there is a example picture of the Excel from where i want to copy the data (there might be data in all columns even there is not in the example) and the Master sheet is exactly the same without the column O.
1661861339740.png
 
Upvote 0
@Newbie_92 what happens with the code below (untested)... obviously remember to edit the file path.
It might also once we get it working to consider using Autofilter to move the data in one action

VBA Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, i As Integer, ActSht As Worksheet

    Set ActSht = ActiveSheet
    LastRow = ActSht.Range("A" & Rows.Count).End(xlUp).Row
   
    Workbooks.Open Filename:="path...."
    Worksheets("Master").Unprotect Password:="password"

    For i = 2 To LastRow

        If ActSht.Cells(i, 15).Value = "Yes" Then

        Range(ActSht.Cells(i, 1), ActSht.Cells(i, 14)).Copy Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
           
        End If
    Next i
   
               
    Worksheets("Master").Protect Password:="password"
    ActiveWorkbook.Save
    ActiveWorkbook.Close
   
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
@Newbie_92 what happens with the code below (untested)... obviously remember to edit the file path.
It might also once we get it working to consider using Autofilter to move the data in one action

VBA Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, i As Integer, ActSht As Worksheet

    Set ActSht = ActiveSheet
    LastRow = ActSht.Range("A" & Rows.Count).End(xlUp).Row
  
    Workbooks.Open Filename:="path...."
    Worksheets("Master").Unprotect Password:="password"

    For i = 2 To LastRow

        If ActSht.Cells(i, 15).Value = "Yes" Then

        Range(ActSht.Cells(i, 1), ActSht.Cells(i, 14)).Copy Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
          
        End If
    Next i
  
              
    Worksheets("Master").Protect Password:="password"
    ActiveWorkbook.Save
    ActiveWorkbook.Close
  
    Application.ScreenUpdating = True
End Sub
This is working perfectly - thanks a lot for your help! :)
 
Upvote 0
You're welcome (when I get a chance I'll post an AutoFilter alternative, which should be faster than that looping code)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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