Copy between workbooks in Excel 2003 vba

Hello Experts,

I have been confused why the following codes cannot work in Excel 2003 :

Please Login or Register  to view this content.

Thx in advance.

Try:

Please Login or Register  to view this content.

Sorry, to make it clear of my problem :

I've a set of codes created in Excel2010 to import/copy from data one workbook1 to another workbook2.
The codes work fine in Excel2010 but fail in Excel2003 in a number of code lines.

I've tried to modify some of the codes to suit Excel2003, but still have the last problem as posted previously.

I now uploaded the file for your checking :

I cannot sort out why the macro only stuck at the second last line, while this line also appear previously without throwing error.

Appreciate your further advice.

Thx !

I cannot test in Excel2003, but according to Microsoft the notation Application.Workbooks(2).Activate should not cause a problem. Try using what "Record Macro" writes as VBA when activating another workbook.

Try:

Please Login or Register  to view this content.

Instead of:

Please Login or Register  to view this content.

No way! I've about 10 macros which are required to be modified from Excel2010 to suit Excel2003.
It is too difficult and time consuming to do the macro recording again in Excel2003, as in fact, all the 10 sets of macros are accomplished partly by recording only - as some codes are inserted after making reference to on-line search result (and cannot be done solely be recording).

My point is why the whole macro only is stuck at the second last line ( i.e. Sheets("Transactions").Select ), but this line also appear in front and no error was thrown out.

Very much perplexed !

You have misread what I wrote.
I did not suggest that you record the macro again . But just use the different style of notation.

After testing to see if it works, I would use "search and replace" to replace:
this notation
"Application.Workbooks(2).Activate"
with this notation
"Windows("zMacro-Import (Excel2003).xls").Activate"
in all your macros - making sure to replace with the correct file name!

I previously also suggested replacing
Sheets("Transactions").Select
with
Sheets("Transactions").Activate
Did this also fail?


Is VBA failing to activate the other workbook or is it failing to find the worksheet?
When it fails has the correct workbook been activated?
What error message are you getting? If none that is very odd

some further thoughts:

Amend both Range("C5") to a cell that contains a value and try inserting these lines:

Please Login or Register  to view this content.

just before this line:

Please Login or Register  to view this content.

Are both lines of the message box identical?


Are you sure that Worksheets(2) the correct reference?
(Will vary if you have some other workbooks open or if workbooks opened in a different sequence)
Try running this code after the other macro fails. This will give the "current" workbook numbers

Please Login or Register  to view this content.

Your original code fails if "2" is not the correct workbook

I've been trying to find out the clues for the error, including using Recording Macro in Excel 2003, and amending the macro to suit but to no avail.

1. I do not specify the workbook name because the name can vary. So instead I use Excel's intrinsic notation of giving the first opened workbook as workbook(1), the second as workbook(2).
2. I've tried changing the codes as you mentioned, but cannot solve anyway.

I'm a bit frustrated, and now upload the source file (workbook(1)) [to be opened first] and the macro file (workbook(2)) [to be opened next], to see if anyone with Excel 2003 can check where the errors come from.

[note that the codes run well in Excel 2010 without problem, but because the macros have to suit some PC with Excel 2003]