How to copy data from multiple worksheets in excel into a single sheet

[abhijit saha]

Hi
i am very new to working with excel and would like the experts help in creating a macro wherein i can copy data from multiple sheets into a single master sheet. However, the number of rows in each sheet is different (max 100) and has a total, and i dont want the total to be copied into the master sheet. Rather, in the master sheet after all the data is copied except the total, a final total is required.
Also please note that the individual sheets have serial numbers which i do not want to be copied into the master sheet, instead after copying the rest of the data a final serial numbering should appear in the final sheet.
The individual sheets also contain vlookup formula, which is helping me in fetching data from other workbooks and i do not want the cells which contain n/a to appear in the final sheet due to the use of formula. My search figure is account number against which account names are populated in the individual sheets, i want the macro to copy only the rows which contain these account numbers and the corresponding data in those rows to the master sheet, and not the rows which do not have account numbers but the corresponding cells show n/a due to preapplied formulas.

Please let me know at the earliest, if required i can upload the file for ur perusal.

[arlu1201]

Please upload the sample file -

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.