Extracting Multi-sheet Common Range To (stacked) .txt

EXTRACTING MULTI-SHEET COMMON RANGE TO (STACKED) .TXT

Excel 2003
Across 25 consecutive sheets I wish to File:Save:"extract" the identical range Z3..AF64 in each sheet and save this 3D range to one grand (stacked) text file (ie the range extracted from each sheet is stacked vertically in the output .txt)

NB In Lotus 123 you simply highlight the 3D range and "Save the Selected Range" as .TXT if the File|Save Dialogue.

Below is LotusScript code that automates this task in 123

' ----------------------------------------------------- AdjEdit_dump
[Window 1].Activate
[UR_:Z3].Select
Selection.Reshape [UR_:Z3..CPIFD_:AF64]
[<<C:\data\WS3\RES_AVE8_EIU.123>>].TextCodePage = $DOS
Selection.RangeExtract "C:\clients\EIU\RES_dmp\EIU_AdjEdit_dump.TXT",,"Text (TXT)",False,"",$ExtractValuesAndFormulas
' -------------------------------------------------------

Any thoughts on how to achieve the above in Excel 2003 appreciated.
Are there any add-ins to achieve this (simple !) task in Excel or will it require VBA code ?

Hi
Try the following macro codes

Please Login or Register  to view this content.

run the macro. It inserts a sheet called consolidated and collates data from Z3:AF64 of all sheets and saves it as text file
Ravi

Ravi

Most Kind

Seems to be a Run Time error 1004 on following line :

Worksheets("consolidated").SaveAs Filename:="C:\DATA\WS3\AdjEdit_Ravi.txt", FileFormat:=
_xlText, CreateBackup:=False

Anything obvious ?

Its a bit problematic inserting a new sheet:
-can it be inserted as the first (or last) sheet ?
- can this new sheet be deleted after .TXT file dumped (as need a clean txt dump everytime to completely overwrite the previous dump)

Not entirely sure if the text file will be stacked properly !
ie need to ensure the rectangular shape/range is manintained in the txt dump and that it doesnt just dump each cell on top of the last cell in one "continuous column" text file !