Macro for combining multiple sheets to one sheet with a condition

Hi All,

We are using Macro to merge all the sheets of an excel file to one sheet and then generating the Merge file into a text format using | delimiter.

I am attaching the Macro code for the same which we are using now.
The values given in the sheets are just random values.

New requirement:
For each sheet there are fixed limits for number of Columns.
Sheet name Total No of columns
0000 6
1000 42
2000A 25
2000S 24
2100 16
3000 23
3100 15
4000 33

While i generate the values from the merge sheet to a text file i need all these files in the text file even if they are empty in the merge sheet
For example:
in sheet 1000 there should be values in 42 columns (there is no fixed limit for rows).
But suppose, before merging I am filling only 38 columns and i will merge the sheet.
While i generate it to a text file i should get all the 38 columns + the empty columns till 42 should be displayed with delimiter without any space.

Hope you got my query.
Please help me with this.

Thanks & Regards,
Asha

You could use this macro:

Please Login or Register  to view this content.

Regards,
Antonio

Thanks Antonio for the quick help,

We are able to generate the file as required, but there are 2 issues
1) We are getting extra spaces at the top of the record, which is not required
2) When we generate the file in text format we are getting a Run time error 91, even then the file is generated.

Can you please check this once
I am attaching the screenshot for the error we are getting. I am not able to attach the the data file generated.

Thanks & Regards,
Asha

Hi,

We debugged that code and changed
For Each mySh In ThisWorkbook.Sheets
to the below mentioned to remove the spaces
For Each mySh In ActiveWorkbook.Sheets(Array("0000", "1000", "2000A", "2000S", "2100", "4000", "3000", "3100", "201"))
After this change that Runtime error 91 was not dispalyed.

Thanks for the help.
This thread can be closed.

Regards,
Asha

I tried my previous code and it was running on my pc but now I modified macro to filter shets and to not export blank rows:

Please Login or Register  to view this content.

Regards,
Antonio