Macro Printing

[MrRJ]

Hello,
I would like to print several reports with a click of a macro. What I
currently have is a drop down box where I make my selection then I click on a
macro which hide/unhide columns based on my drop down selection.

Is there a macro that I can setup where for every drop down selection there
is, run it then print it? I currently do that 14 times, it would be nice to
avoid that.

Thanks,
MrRJ

 

[Rick Rothstein]

It's a little hard to answer that question directly for you as you didn't
show us what is in the drop down box nor the code that executes your
selections. Generally, you should be able to wrap your current macros code
with a For Each loop in which your In object would be an Array function call
with your 14 options listed as its arguments. For example, if your drop down
box items were "Report1", "Report2", "Report3" and so on, then you should be
able to do something like this in your macro...

Dim V As Variant
'
' Other Dim statements here
'
For Each V In Array("Report1", "Report2", "Report3"...etc)
'
' Your existing macro code goes here although however you
' pass the drop down item text to the code, you would now
' use the V variable in its place
'
Next

 

[MrRJ]

Hi Rick, sorry for not responding sooner. I had left for the day when you
responded. Here is my code that I use. This code is used after I make my
drop down selection. Not sure what you mean by "however you pass the drop
down item text to the code"?
Hope this helps you.

Private Sub CommandButton12_Click()
Range("A:HB").EntireColumn.Hidden = False

For Each c In Range("C1:HI1")
If c <> 1 Then Columns(c.Column).Hidden = True
Next c

Range("B10").Select
End Sub

 

[MrRJ]

Rick,
Are you available?

It's a little hard to answer that question directly for you as you didn't
show us what is in the drop down box nor the code that executes your
selections. Generally, you should be able to wrap your current macros code
with a For Each loop in which your In object would be an Array function call
with your 14 options listed as its arguments. For example, if your drop down
box items were "Report1", "Report2", "Report3" and so on, then you should be
able to do something like this in your macro...

Dim V As Variant
'
' Other Dim statements here
'
For Each V In Array("Report1", "Report2", "Report3"...etc)
'
' Your existing macro code goes here although however you
' pass the drop down item text to the code, you would now
' use the V variable in its place
'
Next