Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro??
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 -- HTH, Barb Reinhardt "Steve" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro??
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 "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Steve" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro??
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 "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Steve" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro??
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 wrote: 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 "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Steve" wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro??
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" wrote: 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 wrote: 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 "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Steve" wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro??
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 wrote: 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" wrote: 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 wrote: 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 "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Steve" wrote: 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Macro??
Yep that's exactly what i'm looking for.
thank you!! Steve "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Steve" wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Loop | Excel Discussion (Misc queries) | |||
Loop Macro | Excel Programming | |||
Help with using a Macro Loop | Excel Discussion (Misc queries) | |||
Macro Loop | Excel Programming | |||
Do until loop with use of another macro in loop | Excel Programming |