Macro that loops through and runs existing macro and print

[Mattandy55]

Hi there,

I am a little stuck with an enhancement to something that already exists. I have the below existing code that when a cell in that range is triggered it runs the code depending on which column. However, I need to add 2 buttons, that would loop through the whole column and run offer macro, print, and then run the next offer macro for the next record, and so on. Basically, the goal would be to run the existing macro offer, print that page, and then do it again for as many records as necessary without individually clicking each one. Also, it would be great that this loop worked for only records present if there was a filter applied to the data. Any help you can provide would be greatly appreciated. Please let me know if there are any items I can help further elaborate on.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B2:B2000")) Is Nothing Then
            Call offer
    End If
    End If

If Selection.Count = 1 Then
    If Not Intersect(Target, Range("C2:C2000")) Is Nothing Then
            Call comp
        End If
        End If
End Sub



Sub offer()

    Sheets("SelectData").Range("C1").Value = ActiveCell.Address
    Sheets("Offer Statement").Visible = True
    Sheets("Comp Statement").Visible = False
    Sheets("Employees").Visible = False
    Sheets("Job Title List").Visible = False
    Sheets("Offer Statement").Select


End Sub
Sub comp()

    Sheets("SelectData").Range("C1").Value = ActiveCell.Address
    Sheets("Offer Statement").Visible = False
    Sheets("Comp Statement").Visible = True
    Sheets("Employees").Visible = False
    Sheets("Job Title List").Visible = False
    Sheets("Comp Statement").Select


End Sub

Sub ReturntoEmployees()
Sheets("Employees").Visible = True
Sheets("Comp Statement").Visible = False
Sheets("Offer Statement").Visible = False
Sheets("Employees").Select
End Sub

 

[DanteAmor]

Basically, the goal would be to run the existing macro offer, print that page, and then do it again for as many records as necessary

What is the name of the sheet where the records are?


These lines are what the "offer" macro contains.

Sub offer()
    Sheets("SelectData").Range("C1").Value = ActiveCell.Address
    Sheets("Offer Statement").Visible = True
    Sheets("Comp Statement").Visible = False
    Sheets("Employees").Visible = False
    Sheets("Job Title List").Visible = False
    Sheets("Offer Statement").Select
End Sub

For each record of sheet "x" the "offer" macro will be executed.
Do you need the macro to do anything additional? I'm mentioning it because in your goal you mention "printing that page", but which page?

Can you explain a bit more why you need to make some sheets visible and why hide other sheets?

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[Mattandy55]

Hi there DanteAmor,

So the offer macro opens up a worksheet called offer statement. It is populated based on the selection to fill in the correct info based on the row. The master data for all the records is on the "Employees" worksheet. There are multiple formulas on the offer statement worksheet so when I click on the individual record the formulas will populate the offer statement worksheet appropriately.

So I would need it to loop through and run the offer macro, print the offer statement worksheet for that records, and then replicate for the next record, and so on. The hiding of the individual sheets is more for a cleaner user look when running 1 off selections. Also, there is a second macro called "comp" that runs the same exact macro as offer, but it opens a offer statement worksheet which has some differences between the offer worksheet. So that is why there are some hiding of worksheets when running the macro.

 

[DanteAmor]

I need to add 2 buttons, that would loop through the whole column and run offer macro, print

Put the following macro for the offer button:

Sub offer_button()
  Dim c As Range
  
  For Each c In Sheets("Employees").Range("B2", Sheets("Employees").Range("B" & Rows.Count).End(3))
    Sheets("SelectData").Range("C1").Value = c.Value
    Sheets("Offer Statement").PrintOut
  Next
End Sub

Put the following macro for the comp button:

Sub comp_button()
  Dim c As Range
  
  For Each c In Sheets("Employees").Range("C2", Sheets("Employees").Range("C" & Rows.Count).End(3))
    Sheets("SelectData").Range("C1").Value = c.Value
    Sheets("Comp Statement").PrintOut
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Delete or comment the codes you currently have.

 

[Mattandy55]

nd(3

Thank you Dante.. I have plugged in the coding and tried to execute the button on either one.. both come up with the 400 error

 

[Mattandy55]

Actually I was able to work around the 400 error by moving the new coding to a new module.. however I am getting an error in the code at the Sheets("XXX").Printout stage. It is saying printout method of worksheet class failed

 

[DanteAmor]

Actually I was able to work around the 400 error by moving the new coding to a new module.

Good


Try:

Sub offer_button()
  Dim c As Range
  
  With Sheets("Offer Statement")
    .Visible = True
    For Each c In Sheets("Employees").Range("B2", Sheets("Employees").Range("B" & Rows.Count).End(3))
      Sheets("SelectData").Range("C1").Value = c.Value
      .PrintOut
    Next
    .Visible = False
  End With
End Sub

Sub comp_button()
  Dim c As Range
  
  With Sheets("Comp Statement")
    .Visible = True
    For Each c In Sheets("Employees").Range("C2", Sheets("Employees").Range("C" & Rows.Count).End(3))
      Sheets("SelectData").Range("C1").Value = c.Value
      .PrintOut
    Next
    .Visible = False
  End With
End Sub

 

[Mattandy55]

Good


Try:

Sub offer_button()
  Dim c As Range
 
  With Sheets("Offer Statement")
    .Visible = True
    For Each c In Sheets("Employees").Range("B2", Sheets("Employees").Range("B" & Rows.Count).End(3))
      Sheets("SelectData").Range("C1").Value = c.Value
      .PrintOut
    Next
    .Visible = False
  End With
End Sub

Sub comp_button()
  Dim c As Range
 
  With Sheets("Comp Statement")
    .Visible = True
    For Each c In Sheets("Employees").Range("C2", Sheets("Employees").Range("C" & Rows.Count).End(3))
      Sheets("SelectData").Range("C1").Value = c.Value
      .PrintOut
    Next
    .Visible = False
  End With
End Sub

Hi Dante, It looks like I have been able to get it to print. My issue now is the sheets Offer Statement or Comp Statement are being filled out like when clicking as a one off. The select data C1 is the reference point for the comp or offer statement tabs to fill out with lookups. Is there anyway as its looping to populate the column/cell number for each individual row in the loop? I need to to work the same as the original code I posted, but just print in addition and loop through each row. Right now is prints but all my formulas on the statement tabs are coming through as #REF because the select data C1 isn't picking up the specific cell Like EmployeesC13, then EmployeesC14, etc..

 

[DanteAmor]

Let's go back to the beginning because I'm not understanding.
It would help me a lot if you answer my questions with one word or a simple yes or no?

1. What is the name of the sheet where you have the following code?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
        If Not Intersect(Target, Range("B2:B2000")) Is Nothing Then
            Call offer
    End If
    End If

If Selection.Count = 1 Then
    If Not Intersect(Target, Range("C2:C2000")) Is Nothing Then
            Call comp
        End If
        End If
End Sub

2. Did you delete the code you had on the sheet? If not, then delete it.

3. According to your code the selection of cells starts in cell B2, correct?

4. Currently if you select cell B2, the macro takes that data from cell B2 and puts it on the "SelectData" sheet in cell C1, correct?

5. Do you want to print this sheet: "Offer Statement"?

6. Is it necessary to hide these sheets?
Sheets("Employees").Visible = False
Sheets("Job Title List").Visible = False

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------