VBA runtime error 1004 when copy and pasting

0 votes

Although it actually copies and pastes from one workbook or sheet to another, I keep getting the runtime error 1004: Pastespecial function of range class failed.

The code below does the task, but I still receive the error. I'm sure there is a better way to write the code, but I'm still very new and learning. What can I do to make things better?

Sub CopyWorksheet()

    Dim x As Workbook
    Dim y As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet


    '## Open both workbooks first:
    Set x = Workbooks.Open("C:\Users\KimBush\Documents\NP Credentials Project\Greater than 30 days    project\Macro Testing\NPPIndependentStatusReport")
    Set y = Workbooks.Open("C:\Users\KimBush\Documents\NP Credentials Project\Greater than 30 days project\Macro Testing\DKC-IKC NP Credentialing Update Testing")


    'Now, copy what you want from x:
    x.Sheets("Sheet1").Range("A1:P10781").Copy

    Set ws1 = x.Sheets("Sheet1")
    Set ws2 = y.Sheets("Source")

    ws1.Cells.Copy ws2.Cells
    'y.Close True
    'x.Close False

    'Now, paste to y worksheet:
    y.Sheets("Source").Range("A1").PasteSpecial

    'Close x:
    'x.Close

End Sub
Feb 23, 2023 in Others by narikkadan
• 63,420 points
625 views

1 answer to this question.

0 votes

Try this:

Sub CopyWorksheet()

    Dim x As Workbook
    Dim y As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet

    '## Open both workbooks first:
    Set x = Workbooks.Open("C:\Users\KimBush\Documents\NP Credentials Project\Greater than 30 days    project\Macro Testing\NPPIndependentStatusReport")
    Set y = Workbooks.Open("C:\Users\KimBush\Documents\NP Credentials Project\Greater than 30 days project\Macro Testing\DKC-IKC NP Credentialing Update Testing")

    Set ws1 = x.Sheets("Sheet1")
    Set ws2 = y.Sheets("Source")

    ws1.Cells.copy Destination:=ws2.Range("A1")

    '...

End Sub
answered Feb 23, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel VBA: Open Hyperlinks in a loop and copy paste download link in a sheet

Refer this tutorial for your solution: https://evermap.com/Tutorial_AB ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,420 points
991 views
0 votes
1 answer

VBA Range.Offset Error 1004 Range beyond scope of sheet Dynamic Range

Check the .Row first: With Sheets("Line 3").Range("G1024").End(xlUp) ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
538 views
0 votes
1 answer

VBA code to select only a table. I am getting a Run-time error '1004'; Method 'Range' of object'_Global' failed

No copy/paste, just direct assignment use.Value Sub Final_Report() ...READ MORE

answered Jan 13, 2023 in Others by narikkadan
• 63,420 points
768 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
17,760 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

When there was no unhidden workbook open ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
668 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,420 points
500 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
689 views
0 votes
1 answer

Type mismatch error when referring to array element by location vba

We utilise MID to parse the string. ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
533 views
0 votes
1 answer

How to make an error flagging array in VBA and translate all array elements as a string message?

In my opinion, using an array in ...READ MORE

answered Mar 17, 2023 in Others by Kithuzzz
• 38,010 points
335 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP