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

[xlyfe]

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.

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. ?

 

[jasonb75]

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?

 

[xlyfe]

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]

Does it work if you select the "FlatPaste" cell and pastespecial manually?

 

[xlyfe]

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]

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]

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]

Here's what the Macro Recorder gives me when I manually complete this copy/paste operation.

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]

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]

See what happens with this,

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