Macro for copying data from different sheets

[Solved/Closed]
Report
-
 NK -
Hello,

I am trying to copy data from different sheets to master sheet under the following criteria

1. Master sheet should be generated when ever I run a macro erasing the previous
master sheet.
2. All the Column headings are identical.
3. For master sheet only one heading should be copied (i.e. first row should not be copied
from the second sheet.)
4. If Zero values in a particular cell is appearing then that row should not be copied.
5. Master sheet should be sorted as per the column I mention in running the macro.(i.e. I
will assign on the message box the field/column that should be sorted in the master
sheet. )

Kindly someone help me in this regard.

22 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
1. I do not like such large files with large number of sheets. It is OK for data base entry. But if you want to write macros,manipulate etc this will be cumbersome. It wold also be very difficult to check whether the macros and formulas get the results you want. You structure it in convenient size. If there is a mistake there will be problem to repair. so many interconnections. SPEND MORE TIME IN DESIGNING THE WORKBOOKS AND WORKSHEET BEFORE STARTING THE WORK.

2. be consistent in naming the workbook, sheets etc. for example you have names sheets as REC-JUL,REC-AUG etc then suddenly you have RE-APRIL and REC-JUNE. change these two sheets to REC-APR and REC-JUN IMMEDIATELY.


3. I have created a copy of flat-wise and called it flat-wise(2). I operated only on this sheet.
4. I introduced a new module-modle2. in this module I have two macros "sorting" and "sub_total" (ignore the third macro anyhow that macro cannot be operated even if you want).
4.I have already operated these two macros. what IT DOES is first it sorts according to flat number and makes subtotals of the relevant columns
5.now in the flat-wise(2) sheet I modified the macros only in columns " MTH.
MAINT PAID " onwards up to "" TOTAL AMT REC "(both colored for the first month) FOR EACH MONTH . I modified only in the first data row no. 3 and copied it down
in these relevant columns instead of the first occurrence of vlookup the sum will be there. There a hardly a very few such occasions and I wonder why this could not have been done manually by you. anyhow it is done. see those formula texts.

6. Now flat-wise(2) is COMPLETE. you need NOT DO ANYTHING. But million dollar question is how to check whether the results are ok. It is mind boggling even to do sample checks.

THE FINAL ADVISE SPEND LOT OF TIME IN DESIGNING THE DATA CONFIGURATION , MAKE IT SIMPLER AND EASY FOR MANEUVERING .DO NOT DO ANY COSMETICS (COLOR, FONT ETC)EXCEPT FOR A FEW ESSENTIAL THINGS FOR IDENTIFYING(NOT COSMETIC) UNLESS ALL MACROS FORMULAS ETC ARE DESIGNED AND TESTED ETC. Even though I could visualize a solution it gave me difficulties in implementing as the sheets are large even column wise and repetitive.

I am sending back the file "ASFOA-2010-11 kishore2.xls" .download that from the following web page.

http://www.speedyshare.com/files/25162449/ASFOA-2010-11_kishore2.xls
Hi,
I need VBA or macro to open 1 from multiple sheets from several excel files. After that copy data(selected columns) from those sheets to one sheet. For example, there are 15 excel files in my folder "data". In each excel file there are ten sheets having names A-J. In every sheet there are several columns of data e.g from A to L. What I need is data of sheet 'K', from columns A, D, F, J from all excel files to be copied in 1 sheet. Can someone help?
Thanks.