Copy Data Between 2 Workbooks

Dear Experts,

I'm at entry level in VBA and usually can only modify recorded macros.

Book1 with sheet named Transaction
Book2 with sheet named TX

The codes is intended to copy variable number of rows from Book1 to Book2, but fails at line 7 :

1 Windows("Book1.xls").Activate
2 Sheets("Transaction").Select
3 Rows("9:9").Select
4 Range(Selection, Selection.End(xlDown)).Select
5 Selection.Copy
6 Windows("Book2.xls").Activate
7 Sheets("TX").Select
8 Range("A9").Select
9 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
10 :=False, Transpose:=False

Grateful if any can help. Thx so much!

Originally Posted by Naveed Raza

try with replaceing Windows with Workbooks

Please Login or Register  to view this content.

Replaced Windows with Workbooks, but still stuck at line 7.

Originally Posted by Naveed Raza

Here at my desk its working..............

Please Login or Register  to view this content.

Thanks for your help. I've tried your codes which also work (except should add .xls to Book1 / Book2) ONLY IF I created new set of files for Book1 and Book2. That is, originally Book1 is actually an exported Excel account ledger Excel file which I suspect has been set with a different working environment that rendered the codes not working at line 7.

So I wonder if there are any codes that can be set at top of the codes in order to restore the VBA environment variables to normal working environment, so that they can work with the exported account ledger Excel file.

Kindly please further advise. Thx again.

I'm little scared with your long string of characters. In any case, thanks for your kind help indeed.

But in fact, my problem is only partially solved, because I still cannot understand why the codes do not work in my original Book2.xls file. Please advise how to send over to you for checking.

Originally Posted by john55

the only way is to upload those workbooks (1 and 2) having some data (removing sensitive data) and perhaps someone will help you

In this case, I would be most grateful if you could help check the attached 2 files :
Book1.xls and Book2.xls
(macro in Book2.xls and Ctrl + i to execute, by copying data from Book1.xls to Book2.xls)

Thank you for your advice, as I didn't realize there are other codes attached to each of the worksheet tabs. These codes should possibly be written by IT Dept for extraction of accounting data to Excel.

These codes might have been sneaked into the Book2.xls file somehow when I tried to copy data. No wonder I found at times that the codes failed to work mysteriously on other occasions.

I'm so happy that the problem can be solved, and thanks again.