Combining two macros

[catalina]

Hi Everyone

I have two macro and I cant figure out how to get them to work with each other.

The first Macro I have will print to pdf and use a specific file name and path location as indicated in cell N5

The second macro will automatically print from a data list the entire list.

How do I get these two macro together so that I can print my entire data list to PDF using a specific file name which is indicated in a cell as well as saving it to a specific folder?

Macro 1

Sub Macro5()

s = Range("N5").Value
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        s, Quality:=xlQualityStandard, IncludeDocProperties _
        :=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Macro 2

Sub PrintValidationChoices()
Dim r As Long, i As Long

r = Range("P1 : P22").Cells.Count

For i = 1 To r
    Range("A1") = Range("P1 : P22").Cells(i)
    ActiveSheet.PrintOut Copies:=1
Next i

End Sub

 

[NARAYANK991]

Hi ,

You say the second macro prints out a data list.

What is the reason for the For ... Next loop ?

Within the loop , the following is being done :

1. On each pass , an element from the range P1 : P22 is being transferred to the same cell A1.

2. On each pass , the active sheet is being printed out.

What is the connection between the data list and the active sheet being printed out ?

Can you upload your workbook with the data and the code in it ?

Narayan

 

[catalina]

Hi Narayan,

Thank you so much for helping me! I have an investor statement which I would like to be able to print to pdf all of their statements in one shot. I am trying to build a macro that filters through my data list which are investor names and saves them in a specific folder on my computer with a specific name. Also each investor statement is slightly different reason why I have the "unhiderowsand columns" and "Hiderows" macros below which basically hides rows is there are zeros. The macro is working but I cant get it to loop back and go to the next item on my data list. Do you know how to make this loop back?

Sub PrintValidationChoices()
Dim r As Long, i As Long

s = Range("R5").Value
r = Range("v3:v5").Cells.Count

For i = 1 To r
    Range("N3") = Range("v3:v5").Cells(i)
    unhiderowsandcolumns
    HideRows
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        s, Quality:=xlQualityStandard, IncludeDocProperties _
        :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
       
Next i

End Sub

 

[NARAYANK991]

Hi ,

Sorry , but I am not able to understand how your data is laid out. Probably if you could upload your workbook , it would help.

I understand that in some area of your worksheet you have a list of all the investors ; I also understand that in some other area of your worksheet you have the statements for these investors.

You want to select each investor in turn and print out their statement.

How the macro that you have posted is doing this I don't know. As far as I can see , it does nothing like that.

If you want this done , I think you need to upload your workbook instead of trying to get someone to fix this macro.

Narayan

 

[catalina]

Hi Narayan,

attached is a sample file of what I am trying to do. The first sheet is the statement that I am printing for each investor

 

[NARAYANK991]

Hi ,

I am not too sure about this , but see the attached file.

Narayan

 

[catalina]

Thank you so much! I was able to use your macro to fix mine. I really appreciate it!