Hoping a guru can help this novice out with a simple macro

[slim]

Hi everyone. I'm new VBA and have very minimal knowledge of most VBA
commands, however, I can understand how simple macros work by reading them.
I'm hoping that someone here can help me out with a macro I'm attempting to
write.

The macro is for a monthly financial report. The spreadsheet consists of
two worksheets. The first worksheet is the report, and second is a list
numbers (departments). What I would like the macro to do is:

1. Take the first cell of the list of department numbers from the second
sheet (ie. contents of cell A1) and copy it into a cell on sheet 1 (ie. cell
B5).

2. Perform a manual calculation (what would happen after pressing the F9
key). This step is actually the easy one that I can figure out.

3. Supress all rows that have a balance of 0. For example, if all dollar
amounts on a row add up to 0, this row would be hidden.

4. Save the file with the filename of the department number.

5. Unsupress rows from step #3.

6. Repeat this entrie process for all departments, so it would go back to
sheet #2, and advance to the second number on the list...and continue until
it gets to the end of the list.


I would be very greatful if someone could shed some light on this task.
Even if someone could tell me the basic methods for these steps, I could
work with that and research those methods.

Thanks in advance for any help.

 

[Tushar Mehta]

You can create the basic code to do the work for one department. To do
so turn on the macro recorder (Tools | Macro > Record new macro...),
execute the steps you've outlined, and turn off the macro recorder.
Now, switch to the VBE and you will find that XL has generated the
necessary code.

Share that code in a follow up message in this discussion and someone
should be able to show you how to generalize it to multiple
departments. Please post that code as text in the message and not as
an attachment.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[slim]

Thanks for the reply. I don't have much yet...but here is what I have,
which is obviously pretty much nothing.

Sub Macro1()

Sheets("Cost Centers").Select
Range("A1").Select
Selection.Copy
Sheets("Template").Select
Range("B5").Select
ActiveSheet.Paste
Application.Run "TM1RECALC"
ChDir "C:\TestReports\"
ActiveWorkbook.SaveAs Filename:= _
"C:\TestReports\TestReport.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


Here are the things I need to do:

1. I need to add some type of step after the Application.Run "TM1RECALC"
line that will have the macro hide certain lines. I have no idea how to do
this. If the sum of all the dollar amounts in the line = 0, then the line
should be hidden. This step is going to need to reverse itself after the
save part.

2. I need the macro to loop so it goes through all the steps with each
department number until it reaches the end of the list. For example, on the
second loop, instead of copying the contents of cell A1, it would advance to
A2, up until it gets to the end of the list.

3. I would like the macros to save each time with a different filename. The
filename will be the department number (cell B5).

Thanks again for any help.

 

[Tom Ogilvy]

Change A7 to indicate where the data starts in Sheet Template.

Sub Macro1()
Dim sName as String
Dim cell as Range, cell1 as Range
Dim rng as Range, rng1 as Range
Dim sh as Worksheet
Sname = ActiveWorkbook.FullName
ChDir "C:\TestReports\"
With Sheets("Cost Centers")
set rng = .Range(.Cells(1,1),Cells(1,1).End(xldown))
End with
set sh = Worksheets("Template")
With sh
set rng1 = .Range(.Range("A7"),.Range("A7").End(xldown))
End with
for each cell in rng
sh.Rows.Hidden = False
rng1.Parent.range("B5").Value = cell.value
Application.Run "TM1RECALC"
for each cell1 in rng1
if application.Sum(cell1.Entirerow) = 0 then
cell1.EntireRow.Hidden = True
End if
Next
ActiveWorkbook.SaveAs Filename:= _
"C:\TestReports\" & cell.Value & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Next
' optional if appropriate
Activeworkbook.SaveAs Filename:=sName, FileFormat:=xlWorkbookNormal
End Sub

Should be a start. Test it on a copy of your workbook.

 

[slim]

Tom, Thanks for helping me out, I greatly appreciate it.

A couple of things that I need to work on, a few that I haven't mentioned
before that I just realized.

1. It appears to be hiding too many rows. The template is setup with Data
potentially in Columns A-I.
The macro is hiding several rows even if there is dollar amounts that do not
add to 0. Could this be due to all formulas being in the cells?

Also, there are two other circumstances in which I would not want the rows
to be hidden...if it is a seperator (blank line on purpose), or if it is a
heading row, a row that has a main heading with no dollar amounts next to
them. Is there some way I can designate certain rows to not be deleted?
Mabye if I was to put hidden data on some blanks cells?

2. Instead of saving the entire workbook everytime if goes through the loop,
can I change it to save only the Template sheet?

Thanks again for your help.

 

[Tom Ogilvy]

Sub Macro1()
Dim sName as String
Dim cell as Range, cell1 as Range
Dim rng as Range, rng1 as Range
Dim rng3 as Range
Dim sh as Worksheet
Sname = ActiveWorkbook.FullName
ChDir "C:\TestReports\"
With Sheets("Cost Centers")
set rng = .Range(.Cells(1,1),Cells(1,1).End(xldown))
End with
set sh = Worksheets("Template")
With sh
set rng1 = .Range(.Range("A7"),.Range("A7").End(xldown))
End with
for each cell in rng
sh.Rows.Hidden = False
rng1.Parent.range("B5").Value = cell.value
Application.Run "TM1RECALC"
for each cell1 in rng1
set rng3 = range(cells(cell1.row,1),cells(cell1.row,9))
if application.Sum(rng3) = 0 and _
application.count(rng3)>0 then
cell1.EntireRow.Hidden = True
End if
Next
Activesheet.Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\TestReports\" & cell.Value & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
Next
' optional if appropriate
Activeworkbook.SaveAs Filename:=sName, FileFormat:=xlWorkbookNormal
End Sub

--
Regards,
Tom Ogilvy

Tom, Thanks for helping me out, I greatly appreciate it.

A couple of things that I need to work on, a few that I haven't mentioned
before that I just realized.

1. It appears to be hiding too many rows. The template is setup with Data
potentially in Columns A-I.
The macro is hiding several rows even if there is dollar amounts that do not
add to 0. Could this be due to all formulas being in the cells?

Also, there are two other circumstances in which I would not want the rows
to be hidden...if it is a seperator (blank line on purpose), or if it is a
heading row, a row that has a main heading with no dollar amounts next to
them. Is there some way I can designate certain rows to not be deleted?
Mabye if I was to put hidden data on some blanks cells?

2. Instead of saving the entire workbook everytime if goes through the loop,
can I change it to save only the Template sheet?

Thanks again for your help.

Change A7 to indicate where the data starts in Sheet Template.

Sub Macro1()
Dim sName as String
Dim cell as Range, cell1 as Range
Dim rng as Range, rng1 as Range
Dim sh as Worksheet
Sname = ActiveWorkbook.FullName
ChDir "C:\TestReports\"
With Sheets("Cost Centers")
set rng = .Range(.Cells(1,1),Cells(1,1).End(xldown))
End with
set sh = Worksheets("Template")
With sh
set rng1 = .Range(.Range("A7"),.Range("A7").End(xldown))
End with
for each cell in rng
sh.Rows.Hidden = False
rng1.Parent.range("B5").Value = cell.value
Application.Run "TM1RECALC"
for each cell1 in rng1
if application.Sum(cell1.Entirerow) = 0 then
cell1.EntireRow.Hidden = True
End if
Next
ActiveWorkbook.SaveAs Filename:= _
"C:\TestReports\" & cell.Value & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Next
' optional if appropriate
Activeworkbook.SaveAs Filename:=sName, FileFormat:=xlWorkbookNormal
End Sub

Should be a start. Test it on a copy of your workbook.

--
Regards,
Tom Ogilvy

Thanks for the reply. I don't have much yet...but here is what I have,
which is obviously pretty much nothing.

Sub Macro1()

Sheets("Cost Centers").Select
Range("A1").Select
Selection.Copy
Sheets("Template").Select
Range("B5").Select
ActiveSheet.Paste
Application.Run "TM1RECALC"
ChDir "C:\TestReports\"
ActiveWorkbook.SaveAs Filename:= _
"C:\TestReports\TestReport.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub


Here are the things I need to do:

1. I need to add some type of step after the Application.Run "TM1RECALC"
line that will have the macro hide certain lines. I have no idea how

to
do

this. If the sum of all the dollar amounts in the line = 0, then the line
should be hidden. This step is going to need to reverse itself after the
save part.

2. I need the macro to loop so it goes through all the steps with each
department number until it reaches the end of the list. For example,

on
the

second loop, instead of copying the contents of cell A1, it would

advance

to
A2, up until it gets to the end of the list.

3. I would like the macros to save each time with a different

filename.
The

filename will be the department number (cell B5).

Thanks again for any help.



You can create the basic code to do the work for one department. To do
so turn on the macro recorder (Tools | Macro > Record new macro...),
execute the steps you've outlined, and turn off the macro recorder.
Now, switch to the VBE and you will find that XL has generated the
necessary code.

Share that code in a follow up message in this discussion and someone
should be able to show you how to generalize it to multiple
departments. Please post that code as text in the message and not as
an attachment.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions