Manual Ctrl+C to copy from excel, VBA to paste values in specific range in different workbook (Getting 1004 error)

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Hello,
I've been struggling with this for hours now.
I believe the solution is simple, but I can't figure it out.
The complete steps being taken are as follows...
  1. Open external workbook
  2. Manually select range (which varies often)
  3. Press Ctrl+C or right click select Copy, to copy selected range to windows clipboard
  4. Open workbook where VBA button resides to paste values from clipboard into
  5. Press button
  6. Get error
This is the code that is giving me a (Run-time error '1004': Method 'Range' of object '_Global' Failed).
I've noted below which line is producing this error.

VBA Code:
Sub PstFlatBtn_Click()
Application.ScreenUpdating = False
Application.Calculation = xlManual
    Range("FlatsPaste").PasteSpecial xlPasteValues   'getting the 1004 error here
    Range("FlatsAutoFit").Columns.AutoFit
    Range("EwpAutoFit").Columns.AutoFit
    Range("SidingAutoFit").Columns.AutoFit
    Range("CustomerAutoFit").Columns.AutoFit
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Is anybody able to help me quickly see what I'm doing wrong here?
I've tried multiple ways of addressing this issue, but none have worked, so I'm starting over.
Please let me know if I need to provide more information to help create a clearer picture of the issue at hand.
Thank you in advance to anybody that is able to assist. 🙏
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
First 2 questions.

Does the named range, FlatsPaste, exist in the destination workbook and is the sheet with that range selected?
If it doesn't exist in the correct workbook then it will cause an error. If the sheet is not selected then it could cause an error as the range is not qualified with the sheet name.

Are there any merged cells in the paste range? If there are then it will most likely cause an error.

edit:-

extra question, is the sheet protected?
 
Last edited:

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
First 2 questions.

Does the named range, FlatsPaste, exist in the destination workbook? If it doesn't exist in the correct workbook then it will cause an error.

Are there any merged cells in the paste range? If there are then it will most likely cause an error.
Hi @jasonb75 👋. Thank you for your quick response.

The name range "FlatsPaste" does exist in the desination workbook, and it's referencing a workbook scope of "=PlanElev!$CH$20".
And there are no merged cells in either the copy range or the destination paste range.
Also, destination sheet is not protected.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
Does it work if you select the "FlatPaste" cell and pastespecial manually?
 

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Does it work if you select the "FlatPaste" cell and pastespecial manually?
It does. And what's weird is that this code was working fine before.
I've back traced all my steps from a version of this file that this was working before and can't locate what's different in this newer version that would be triggering this error.
 

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Maybe the source workbook that I'm copying from is trying to takeover as the "activeworkbook" in the code, with which the named range does not exist in?
Should I include an extra step in the code that says to set the workbook and worksheet to be the same that the "paste" button was pressed in?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If pasting manually works where the code fails then that eliminates all of the usual causes for such problems.
If the button and code are located in the destination workbook then there should be no problems there, however if they are not, then that could be causing the problem.
In that case, it would be best to set up a with block to identify the correct workbook.
 

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Here's what the Macro Recorder gives me when I manually complete this copy/paste operation.

VBA Code:
Sub Macro3()
'
' Macro3 Macro
'

'
    Windows("6 N22D A.xls").Activate
    Range("BO9:BX12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("New QF Optimizer (RAH Unbundler) - v3.7.xlsm").Activate
    Range("CH20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("DS34").Select
End Sub
 

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
If pasting manually works where the code fails then that eliminates all of the usual causes for such problems.
If the button and code are located in the destination workbook then there should be no problems there, however if they are not, then that could be causing the problem.
In that case, it would be best to set up a with block to identify the correct workbook.
Both button and code do both reside in the destination workbook.
What is going on?? 🤷‍♂️🤦‍♂️🔨🔨🔨
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,590
Office Version
  1. 365
Platform
  1. Windows
See what happens with this,
VBA Code:
Sub PstFlatBtn_Click()
Application.ScreenUpdating = False
Application.Calculation = xlManual
    With Workbooks("New QF Optimizer (RAH Unbundler) - v3.7.xlsm").Worksheets("PlanElev")
        .Range("FlatsPaste").PasteSpecial xlPasteValues
        .Range("FlatsAutoFit").Columns.AutoFit
        .Range("EwpAutoFit").Columns.AutoFit
        .Range("SidingAutoFit").Columns.AutoFit
        .Range("CustomerAutoFit").Columns.AutoFit
    End With
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,288
Messages
5,635,333
Members
416,855
Latest member
niha

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
Top