asked on

Excel VBA Macro

I am need to create a VBA in Excel, to copy information from the active workbook to a non-active workbook. The non-active file name will be "1mainworkbook". The active workbook could be one of over a 1,000 workbooks.

The issue is that as soon as I save and close the active workbook I lose the ability to paste the information into "1mainworkbook."

This appears to be the line that is causing the issues as I just guessed on how to create.

ActiveSheet.Paste Destination:=Workbook("1mainworkbook.xlsm").Range("a2:h2")
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Subodh Tiwari (Neeraj)

What is on the clipboard before you try to paste?
Destination:=Workbook("1mainworkbook.xlsm").Range("a2:h2") seems to be wrong as it doesn't qualify the sheet reference which is missing here.

And it should be like this...
Destination:=Workbook("1mainworkbook.xlsm").Sheets("Sheet1").Range("a2:h2")

FINNROB

ASKER

Thank you for the effort. Unfortunately, there is nothing in the clipboard before I paste. Current Error MS VBA Compile error: sub or function not defined.

ActiveSheet.Paste Destination:=Workbook("1mainworkbook.xlsm").Sheets("Sheet1").Range("a2:h2")

Does no appear to like the "Workbook"

Subodh Tiwari (Neeraj)

In the below line you are trying to paste the already copied data
ActiveSheet.Paste Destination:=Workbook("1mainworkbook.xlsm").Sheets("Sheet1").Range("a2:h2")

I was asking about the line which is copying the data.

FINNROB

ASKER

Sorry

Here it is
Sheets("INVOICE").Select
Range("J150:Q150").Select
Selection.Copy

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FINNROB

ASKER

Thank you for your help. This worked.

Subodh Tiwari (Neeraj)

You're welcome. Glad to help.