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

 

[xlyfe]

See what happens with this,

Nada. Same error at the same line.
It's got to be something from the source file causing this. But what?

 

[jasonb75]

It's got to be something from the source file causing this.

This is what is confusing me as well, if it works manually carrying out the exact same process then I can see no reason for it to fail.

What happens if you try running the recorded code that you posted earlier?

What about entering the worksheet range into the code instead of the named range? (Try this with your original code and my edit, although I don't expect any difference).