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

Using your item numbers

6, is there any typo error please? Why B14 and B1 mix together?
7, 69 is the original row number before any deletion in 6
8, rows 1 to 12 are now the new 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

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

try something like this

Sub Main()

FName$ = Application.GetOpenFilename("Data files (*.xls), *.xls")
If FName$ = "False" Then GoTo Label1 'User did not select
files
Workbooks.Open FName$

For Each sht In Sheets
If .Name < "Control" Or .Name < "Total" Then
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Range("A:A").Insert
Range("A14:A68") = Range("C2")
Range("69:250").Delete
Range("1:12").Delete
For i = 56 To 2 Step -1
If Range("B" & i) = "" Then Rows(i).Delete
Next i
Range("P:AB").Delete
Range("1:4").Insert
Sheets("Control").Range("A10:C13").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
End If
Next sht
End Sub

We have not built in loops and checks please

"Pele" wrote:

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

Initially, the macro did not work at all because of some syntax error. Below
are the error and the changes I had to make to even make it run. I have also
appended the new version of the macro.

The major problem is that the Macro works only on the ONE worksheet
repeatedly and doesn't move off that sheet. The FOR statement can't seem to
let the macro remember which sheet it had just worked on.

1) I have updated the names of the worksheets that the macro should ignore.
I noticed though that the If statement wasn't working since the macro works
on any sheet highlighted when the workbook was opened (even if the worksheet
should have been ignored).
2) I had to use syntax like sht.Name instead of the .Name you'd used
3) I had to include Label1 refered just above the End Sub statement
4) Here is the updated macro with above 2 changes. ANy help you can render
will be appreciated.

Sub Main()

' Macro recorded 10/17/2005 by Tokunbo Akindele

FName$ = Application.GetOpenFilename("Data files (*.xls), *.xls")
If FName$ = "False" Then GoTo Label1 'User did not select files
Workbooks.Open FName$

For Each sht In Sheets
If sht.Name < "Control" Or sht.Name < "Total Admin" Or sht.Name < "B"
Or sht.Name < "E" Then
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
'Cells.Select
Range("A:A").Insert
Range("A14:A68") = Range("D2")
Range("69:250").Delete
Range("1:12").Delete
For i = 56 To 2 Step -1
If Range("B" & i) = "" Then Rows(i).Delete
Next i
Range("q:AB").Delete
Range("1:4").Insert
Sheets("Control").Range("A10:C13").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
End If
Next sht

Label1:
End Sub


"PY & Associates" wrote:

try something like this

Sub Main()

FName$ = Application.GetOpenFilename("Data files (*.xls), *.xls")
If FName$ = "False" Then GoTo Label1 'User did not select
files
Workbooks.Open FName$

For Each sht In Sheets
If .Name < "Control" Or .Name < "Total" Then
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Range("A:A").Insert
Range("A14:A68") = Range("C2")
Range("69:250").Delete
Range("1:12").Delete
For i = 56 To 2 Step -1
If Range("B" & i) = "" Then Rows(i).Delete
Next i
Range("P:AB").Delete
Range("1:4").Insert
Sheets("Control").Range("A10:C13").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
End If
Next sht
End Sub

We have not built in loops and checks please

"Pele" wrote:

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

sht.name - my mistake
Sheets "B" and "E" - that were to be included earlier
Sheet "Total Admin" was "Total"

Label1 - I guessed you will get over it

Is it working as you wish now please?

"Pele" wrote in message
...
Initially, the macro did not work at all because of some syntax error.
Below
are the error and the changes I had to make to even make it run. I have
also
appended the new version of the macro.

The major problem is that the Macro works only on the ONE worksheet
repeatedly and doesn't move off that sheet. The FOR statement can't seem
to
let the macro remember which sheet it had just worked on.

1) I have updated the names of the worksheets that the macro should
ignore.
I noticed though that the If statement wasn't working since the macro
works
on any sheet highlighted when the workbook was opened (even if the
worksheet
should have been ignored).
2) I had to use syntax like sht.Name instead of the .Name you'd used
3) I had to include Label1 refered just above the End Sub statement
4) Here is the updated macro with above 2 changes. ANy help you can render
will be appreciated.

Sub Main()

' Macro recorded 10/17/2005 by Tokunbo Akindele

FName$ = Application.GetOpenFilename("Data files (*.xls), *.xls")
If FName$ = "False" Then GoTo Label1 'User did not select files
Workbooks.Open FName$

For Each sht In Sheets
If sht.Name < "Control" Or sht.Name < "Total Admin" Or sht.Name <
"B"
Or sht.Name < "E" Then
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
'Cells.Select
Range("A:A").Insert
Range("A14:A68") = Range("D2")
Range("69:250").Delete
Range("1:12").Delete
For i = 56 To 2 Step -1
If Range("B" & i) = "" Then Rows(i).Delete
Next i
Range("q:AB").Delete
Range("1:4").Insert
Sheets("Control").Range("A10:C13").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
End If
Next sht

Label1:
End Sub


"PY & Associates" wrote:

try something like this

Sub Main()

FName$ = Application.GetOpenFilename("Data files (*.xls), *.xls")
If FName$ = "False" Then GoTo Label1 'User did not
select
files
Workbooks.Open FName$

For Each sht In Sheets
If .Name < "Control" Or .Name < "Total" Then
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Range("A:A").Insert
Range("A14:A68") = Range("C2")
Range("69:250").Delete
Range("1:12").Delete
For i = 56 To 2 Step -1
If Range("B" & i) = "" Then Rows(i).Delete
Next i
Range("P:AB").Delete
Range("1:4").Insert
Sheets("Control").Range("A10:C13").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
End If
Next sht
End Sub

We have not built in loops and checks please

"Pele" wrote:

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

To get the macro to work exactly, I made changes to the one you'd sent. I was
able to study what you'd done and looked up some info in HELP. Anyway, it
works fine now. THANKS very much for your insight. Below is the final work.

Toks

Sub FCST_Processor()

' Macro created on 10/17/2005 by Tokunbo Akindele

FName$ = Application.GetOpenFilename("Data files (*.xls), *.xls")
If FName$ = "False" Then GoTo Label1 'User did not select files
Workbooks.Open FName$

For j = 1 To Worksheets.Count Step 1
Worksheets(j).Select
If j < 1 And j < 2 And j < 3 And j < Worksheets.Count Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A:A").Insert
Range("A14:A68") = Range("D2")
Range("69:1000").Delete
Range("1:12").Delete
For i = 56 To 2 Step -1
If Range("B" & i) = "" Then Rows(i).Delete
Next i

Range("q:AB").Delete
Range("1:4").Insert
'Sheets("Control").Range("A10:C13").Copy
'Range("A1").PasteSpecial Paste:=xlPasteValues
Selection.Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Sheets("Control").Range("A10:C13").Copy
Range("A1").Select
Selection.Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("E").Range("B4:M4").Copy
Selection.Range("c4").PasteSpecial Paste:=xlPasteValues
Else
End If
Next j
Worksheets(4).Select


Label1:
End Sub



"PY & Associates" wrote:

sht.name - my mistake
Sheets "B" and "E" - that were to be included earlier
Sheet "Total Admin" was "Total"

Label1 - I guessed you will get over it

Is it working as you wish now please?

"Pele" wrote in message
...
Initially, the macro did not work at all because of some syntax error.
Below
are the error and the changes I had to make to even make it run. I have
also
appended the new version of the macro.

The major problem is that the Macro works only on the ONE worksheet
repeatedly and doesn't move off that sheet. The FOR statement can't seem
to
let the macro remember which sheet it had just worked on.

1) I have updated the names of the worksheets that the macro should
ignore.
I noticed though that the If statement wasn't working since the macro
works
on any sheet highlighted when the workbook was opened (even if the
worksheet
should have been ignored).
2) I had to use syntax like sht.Name instead of the .Name you'd used
3) I had to include Label1 refered just above the End Sub statement
4) Here is the updated macro with above 2 changes. ANy help you can render
will be appreciated.

Sub Main()

' Macro recorded 10/17/2005 by Tokunbo Akindele

FName$ = Application.GetOpenFilename("Data files (*.xls), *.xls")
If FName$ = "False" Then GoTo Label1 'User did not select files
Workbooks.Open FName$

For Each sht In Sheets
If sht.Name < "Control" Or sht.Name < "Total Admin" Or sht.Name <
"B"
Or sht.Name < "E" Then
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
'Cells.Select
Range("A:A").Insert
Range("A14:A68") = Range("D2")
Range("69:250").Delete
Range("1:12").Delete
For i = 56 To 2 Step -1
If Range("B" & i) = "" Then Rows(i).Delete
Next i
Range("q:AB").Delete
Range("1:4").Insert
Sheets("Control").Range("A10:C13").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
End If
Next sht

Label1:
End Sub


"PY & Associates" wrote:

try something like this

Sub Main()

FName$ = Application.GetOpenFilename("Data files (*.xls), *.xls")
If FName$ = "False" Then GoTo Label1 'User did not
select
files
Workbooks.Open FName$

For Each sht In Sheets
If .Name < "Control" Or .Name < "Total" Then
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Range("A:A").Insert
Range("A14:A68") = Range("C2")
Range("69:250").Delete
Range("1:12").Delete
For i = 56 To 2 Step -1
If Range("B" & i) = "" Then Rows(i).Delete
Next i
Range("P:AB").Delete
Range("1:4").Insert
Sheets("Control").Range("A10:C13").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
End If
Next sht
End Sub

We have not built in loops and checks please

"Pele" wrote:

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