LinkBack URL
About LinkBacks
Dear Experts
I am trying to consolidate data from multiple sheets of one workbook put it in one place but with some modifications so that it can be exported to access afterwards. I am seeking help on a macro which will do the following:
1. Delete contents of column A
2. In A3 Type a formula “=LEFT(B3,14)” this will become the column header
3. Then it will go to cell A5 and again type a formula “=IF(B4="",B5,A4)” and copy it all the way down
4. Then it should insert two columns A and B and then go to A3 Type Data I as header and in B3 type Data II as header
5. Each worksheet has a name like XXXXX – A or XXX – B. I want to populate column a with all the characters before – in column A and single character after – in column b
6. Copy entire sheet paste special values
7. Insert Two more column A and B
8. Delete Column P
9. Delete Column S
10. Delete Colum AE
11. Go to column F
12. Delete all the rows if column F is blank and also delete all the rows where text is BOLD (since these are sub headers)
-------- I can stop here and do the following manually ------------------
13. Now starting from column G till AD we have 24 months data and headers are Jan-12, Feb-12…… Dec-13
14. Instead of having it across the columns it should be in rows I mean I would cut all the columns after G (H till AD) and paste them down after leaving a blank row to identify that month has changed and do it till all the data is in column G
15. Populate Column A and B (Year & Month) with the headers of each column pasted in column G
16. Copy and paste column C,D,E & F columns ( as they remain same)
This will clean up the sheet and I will do the same with other sheet and then have all info in 7 columns instead of having it in 30 columns and 25 worksheets
Purpose of cleaning and consolidating the sheets is to create a Pivot Table.
Thanks in advance/VKS
Register To Reply
Bookmarks