Loop Macro??

[Steve]

I think I need to create a loop Macro to do this, but i'm not sure...

Here's the situation:

I have a cell (D8) that contains a drop down box of about 20 unique codes.
When the code is selected it auto-formulates all the necessary information on
the next sheet over (Check Request Sheet).

What I need to be able to do is have a Macro that when I run it, it
automatically goes through and selects each code from the drop down box and
then prints what is on the Check Request Sheet and then goes to the next code
and does the same.

I already have all the formulas and such written to auto-format the Check
Request Sheet. I just need to know how to create a macro that would allow
for the selecting and printing repeating cycle. I was thinking maybe a loop
macro, but I'm not really sure, I'm still trying to read about them to better
understand what they do.

Thanks,

Steve

 

[Barb Reinhardt]

Try this

Sub Test()
Dim myCell As Range
Dim myRangeName As String '<~~I've used a range name, but I think it would
work without
Dim aWS As Worksheet
Dim myValList As Range
Dim myValidation As Range
Set aWS = ActiveSheet

Set myCell = aWS.Range("I3") '<~~replace cell address
myRangeName = Replace(myCell.Validation.Formula1, "=", "")
Set myValList = Range(myRangeName)


For Each myValidation In myValList

myCell.Value = myValidation.Value

Next myValidation

End Sub

 

[Steve]

Works perfectly Barb!! Thank you so much!! I just added in the print out
steps.

Sub Test()
Dim myCell As Range
Dim myRangeName As String
Dim aWS As Worksheet
Dim myValList As Range
Dim myValidation As Range
Set aWS = ActiveSheet

Set myCell = aWS.Range("D8")
myRangeName = Replace(myCell.Validation.Formula1, "=", "")
Set myValList = Range(myRangeName)


For Each myValidation In myValList

myCell.Value = myValidation.Value
ActiveSheet.Next.Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.Previous.Select

Next myValidation

End Sub

Thanks again!

Steve

 

[Steve]

Hey Barb,

I have one adjustment request. Not sure if this is possible but is it
possible to add to our macro something that says if cell L22 = 0.00 then
don't print. I've been trying to figure it out for myself, but I'm stuck.

Let me know if you come up with anything. Thanks!!

Current Marco Code:

Sub Test()
Dim myCell As Range
Dim myRangeName As String
Dim aWS As Worksheet
Dim myValList As Range
Dim myValidation As Range
Set aWS = ActiveSheet

Set myCell = aWS.Range("D8")
myRangeName = Replace(myCell.Validation.Formula1, "=", "")
Set myValList = Range(myRangeName)


For Each myValidation In myValList

myCell.Value = myValidation.Value
ActiveSheet.Next.Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.Previous.Select

Next myValidation

End Sub

 

[Dave Peterson]

So that one cell controls all of the printing???

If yes, then add a little bit of code:

if aws.range("l22").value = 0 then
msgbox "Can't print"
exit sub
end if

'right before this line
Set myCell = aWS.Range("D8")

 

[Steve]

Yes that one cell controls the printing ultimately. Heres the kicker though,
I have about 300 companies in a drop down box. Right now the macro chooses
the first company from the dropdown box, goes to the next sheet, which auto
populates based on the company selected in the dropdown box and then prints.
The macro then returns to the previous sheet, selects the next company from
the dropdown box and repeats the above steps.

The slight modification I want is that I don't need the companies to print
which have a total amount in cell L22 that equals 0.

You're on the right track I believe, but I don't want a message box, I want
it to be all automatic.

Steve

 

[Dave Peterson]

So one cell controls if you should print each company.

Sub Test()
Dim myCell As Range
Dim myRangeName As String
Dim aWS As Worksheet
Dim myValList As Range
Dim myValidation As Range
Set aWS = ActiveSheet

Set myCell = aWS.Range("D8")
myRangeName = Replace(myCell.Validation.Formula1, "=", "")
Set myValList = Range(myRangeName)


For Each myValidation In myValList

myCell.Value = myValidation.Value
ActiveSheet.Next.Select
if activesheet.range("L11").value = 0 then
'don't print
else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
end if
ActiveSheet.Previous.Select

Next myValidation

End Sub

 

[Steve]

Yep that's exactly what i'm looking for.

thank you!!

Steve