Paste entire sheet into another Workbook

[DwinZly]

I am having trouble with a seemingly easy transaction. The user is prompted to open an Excel sheet with data and select the entire worksheet before copying the data. Then they go to the macro workbook (different workbook) where they trigger the macro which should paste the data from the clipboard into the new workbook (followed by compiling etc.) The issue is that I can't get the data to paste from the clipboard into the new workbook. I have the below code, which returns "1004 - Paste Method of Worksheet class failed." Any help to resolve this seemingly easy problem is greatly appreciated.

Sub CompileData()

    Sheets("MB51 Data").Activate
    Cells.Select
    ActiveSheet.Paste
    ActiveWorkbook.Worksheets("MB51 Data").Rows("1:2").Delete Shift:=-4162
    
End Sub

 

[rondeondo]

Hi
I have done a bit of copy and paste between files, something when using "Activesheet" is that refers to the file you're currently focused on, so given you need to switch to a different workbook to trigger your macro it may be that the desired destination sheet can't be found. These things can be tricky to troubleshoot as stepping through the code does change the active file.
An option is to add the opening of the destination file to the macro, so you can then assign the destination file to a variable and address it specifically. Also you can assign a macro to a toolbar button. Create a custom toolbar and add a button that triggers the macro, this saves manually opening the file with the macros.
I have a file with all of my macros in it and have a bunch of buttons for triggering them. This can be handy as you expand on the creativity of vba.

 

[MARK858]

Try

Sheets("MB51 Data").Cells(1,1).PasteSpecial

This is assuming that the users have already activated the destination workbook.
Normally when coding you would include the workbook in the code to save activating it.