Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Required - to process data
I need somebody to help me write a macro to help in automating the processing
of some Excel information collected from Users. Below is the background and the question. BACKGROUND Our department sends out an excel spreadsheet to Users for collecting budget information and a macro is then used to process the collected information. The problem is that the budget template has changed a lot and the old macro won't work anymore. The new template (workbook) MUST have 4 sheets called "Control, "Total", "B" and "E". The User can add as many sheets to the workbook as they want BUT the added worksheets MUST be between the worksheets called "B" and "E". They can name the added worksheets anything they want. They will then send their information to me. I then need to process the workbooks and load them to a database. I need a macro to help automate the processing of each submitted workbook. MACRO REQUIREMENTS Here is what the macro should be able to do. 1) The macro needs to reside in a separate workbook from the workbooks submitted by the Users. The workbook is called FCST MACRO. It would be nice if the macro can do a FileOpen so that I can point to which User file needs processing. 2) When the User workbook is open, the macro should highlight (select) all the worksheets between the sheet called "B" and the sheet called "E". 3) For all selected sheets, macro needs to copy the contents of the worksheets (Edit<Copy) and then do Edit<Paste Special<Values. That is, for all the sheets selected, paste the contents unto itself so that all the equations are now values. 4)For all selected sheets, select Column A and add a new column (the new column will now be column A) 5) For all selected sheets, Copy cell C2 and paste into A14:A68 6) For all selected sheets, macro should check contents of cells B14:B68, and if the cell is blank, then the row should be deleted. That is, go iteratively from B1 to B68. 7) For all selected sheets, Delete rows 69:250 8)For all selected sheet, Delete rows 1:12 9) For all selected sheets, delete columns P:AB 10)For each selected sheet, insert 4 rows in row 1 (so, former row 1 is now row 5) 11)Copy contents of Control!A10:C13 and paste into A1 for all selected worksheets 12) Deselect all the sheets Note that macro shouldn't save workbook. Any help would be appreciated. Pele |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to automate process | Excel Discussion (Misc queries) | |||
How to count process running time ( process not finished) | Excel Programming | |||
How to count process running time ( process not finished) | Excel Programming | |||
Run a macro batch process | Excel Programming | |||
Removing lines of data that do not contain required data (macro) | Excel Programming |