PDA

View Full Version : Confusing issue with Copying and moving work tabs



Poundland
07-14-2011, 02:37 AM
Hi guys,

I have some code that works in both 2007xl and 2003xl, and is actively working in vba codes ran on both variants, so I know that the code works...

Now the strange thing is that on one particular VBA project on xl2007 the code does not work, and I cannot for the life of me figure out why.. when it works in other projects... is there something fundamental that I am missing, perhaps file formats, permissions etc

The code is below, the error message received states Subscript our of range, yet I know the the Tab to be copyed and the destination file and tabs are all correctly named..


Sheets("Essentials Store Summary").Copy After:=Workbooks( _
"Availabilty Template.xls").Sheets("B Inv Missing Lines")

Aflatoon
07-14-2011, 06:39 AM
Where is the code? In a normal module, ThisWorkbook module, elsewhere? My first guess would be that the names are not correct, but if you perchance had the code in the ThisWorkbook module of a workbook that does not contain the "Essentials Store Summary" sheet, that would also cause RTE9.

Poundland
07-14-2011, 06:48 AM
The code has not changed, the work tab names have not changed, and the code has not changed its position within the module, we swapped over to XL2007 and the code stopped working as it did in XL2003..

But... the exact same code albeit with different named tabs works in a different module, also ran on XL2007???

Aflatoon
07-14-2011, 06:50 AM
Three possibilities spring to mind:
1. You have misspelt Availability in "Availabilty Template.xls"
2. The other workbook may have been saved as an xlsx, xlsm, or xlsb file.
3. The other workbook may not be open, or not open in the same instance of Excel.

Poundland
07-14-2011, 06:54 AM
All of those I have thought of and checked for, and none of them account for the issue..

The code as it stood worked in XL2003, and the same code with different work tabs works in a different macro in XL2007...

It really is baffleing... :dunno