Prompt Error Messages if Row exceeded Limit

[Apple Ling]

Hi, I have a macro file that allow me to combine all the files in the same workbook. My problem now is to prompt an error message if my combined sheet exceeded row limit. I'm not very good in VBA and i hope someone can amend the macro file for me.

Thank you very much

Apple

Please Login or Register  to view this content.

[royUK]

Try this

Please Login or Register  to view this content.

[foxguy]

Roy;
In the previous message you have:

Please Login or Register  to view this content.

1) What does the final (2) do?
2) Wouldn't this overwrite the last row of the data already on the sheet?

[royUK]

it's a typo

[Apple Ling]

Hi Roy, thanks for your reply but the code doesn't work well at my end. It's only copy the header in to Combined sheet. I don't know what is the problem. I attached here with the sample file. May be you could have a look

Thanks
Apple

[foxguy]

Hi Apple;
Change the following lines:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Finished product:

Please Login or Register  to view this content.

[Apple Ling]

Hi foxguy, i have amended the given code and it looks fine . However, if one of the data source does not place it correctly, the data from that particular sheet will not be transferred. Attached here with the sample output. The data on sheet 3 does not transfer to Combined sheet.

Please Login or Register  to view this content.

[foxguy]

Hi Apple Ling;

Techinically, this new problem should be in a new thread, because it has nothing to do with the original question. So don't be surprised if a moderator says something about it.

CurrentRegion doesn't select all cells in the sheet. It only selects the cell that are surrounded by spaces. In your sample there are blank rows between row 1 and row 17, so CurrentRegion doesn't get them all.
UsedRange is what you should use to get all used cells in the sheet.

Please Login or Register  to view this content.

One warning about UsedRange. It doesn't always start in A1. If Row 1 is blank, and Column A is blank, then UsedRange's top left corner will be B2.
If you always want to have A1 as the top left corner then use this

Please Login or Register  to view this content.

Another note:
I recommend not using

Please Login or Register  to view this content.

Sheets(1) is not always the first sheet.

A safer way:

Please Login or Register  to view this content.

One Final Note, for more user friendly code:
Your section:

Please Login or Register  to view this content.

would be better like this:

Please Login or Register  to view this content.

That way, you don't have to delete the sheet every time you run the macro.[/SIZE]