Thanks for taking the time to looka t this...Your solution will really be
helpful to me.
See my answers below your question. I have also rearranged the task sequence
to address your concerns.
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, Delete rows 69:250
7)For all selected sheets, Delete rows 1:12
8) For all selected sheets, macro should check contents of cells B2:B56
(formerly B14:B68 before task #7 above), and if the cell is blank, then the
row should be deleted. That is, go iteratively from B2 to B56.
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 (paste Special<Values)
12) Deselect all the sheets
"PY & Associates" wrote:
Using your item numbers
6, is there any typo error please? Why B14 and B1 mix together?
TAIt is a typo...the macro needs to check cells B14 to B68 and if they
are blank, the rows should be deleted.
7, 69 is the original row number before any deletion in 6
TAYes, you are right. So, I guess task #7 should be done before task #6
8, rows 1 to 12 are now the new rows
TARows 1-12 were the old rows.
Please clarify
"Pele" wrote:
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