Macro Required - to process data

[Pele]

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 File>Open 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

[PY & Associates]

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 File>Open 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
>
>

[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 File>Open 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?
TA>>>It 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
TA>>Yes, you are right. So, I guess task #7 should be done before task #6

> 8, rows 1 to 12 are now the new rows
TA>>>Rows 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 File>Open 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
> >
> >

[PY & Associates]

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 File>Open 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?
> TA>>>It 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
> TA>>Yes, you are right. So, I guess task #7 should be done before task #6
>
> > 8, rows 1 to 12 are now the new rows
> TA>>>Rows 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 File>Open 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
> > >
> > >

[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 File>Open 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?
> > TA>>>It 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
> > TA>>Yes, you are right. So, I guess task #7 should be done before task #6
> >
> > > 8, rows 1 to 12 are now the new rows
> > TA>>>Rows 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 File>Open 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
> > > >
> > > >

[PY & Associates]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 File>Open 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?
> > > TA>>>It 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
> > > TA>>Yes, you are right. So, I guess task #7 should be done before task
#6
> > >
> > > > 8, rows 1 to 12 are now the new rows
> > > TA>>>Rows 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 File>Open 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
> > > > >
> > > > >

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 File>Open 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?
> > > > TA>>>It 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
> > > > TA>>Yes, you are right. So, I guess task #7 should be done before task
> #6
> > > >
> > > > > 8, rows 1 to 12 are now the new rows
> > > > TA>>>Rows 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 File>Open 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
> > > > > >
> > > > > >
>
>
>