Need help with printing VBA Macro

[tonyjyoo]

Hello,

I need help with a printing macro. I have 4 sheets labeled "FINRA PV" and "FINRA Download" along with its corresponding months, and would like to automatically print all of them.

However, I change the name of one them every month. Currently this is what I have:

 Sheets("FINRA Jul PV").Select
    Range("F19").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("FINRA Jul Download").Select
    Range("R10").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("FINRA Jun PV").Select
    Range("F16").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("FINRA Jun Download").Select
    Range("I11").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

Which would print my "July" and "June" tabs. But next month it wont work because I will be re-naming the sheets to "July" and "August".

How do I incorporate using the Sheet "number" instead of the title? The sheet numbers displayed in VBA are Sheets 10,11,15, and 16.

 

[pbcnick]

Hi,
you could check for the current month you are in, translate the month number to a value (e.g. 1 = Jan, 2 = Feb)... and then loop through all the sheets and if the month's name is in the Sheet name then print it.
For example.. In the month of August you could print any sheet that has 'Aug' in the sheet name. This would apply to a sheet named "FINRA Aug PV" and also "FINRA Aug Download".
This way you can keep using simple names for the sheets and print only the current month, prior month or which ever month you need to print.

Additionally, you could add any other standard names if there is a sheet you print every month.

 

[pbcnick]

How do I incorporate using the Sheet "number" instead of the title? The sheet numbers displayed in VBA are Sheets 10,11,15, and 16.

how about this? (you can choose to comment out or uncomment any ONE of the opening IF statements, according to your needs)

Option Explicit
Sub print_sheet_acc_to_month()

Dim strMonths As Variant
Dim curMonth As Integer
Dim Ws As Worksheet

curMonth = Month(Now)
strMonths = Array("none", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

For Each Ws In Worksheets

[COLOR="#008000"]    ' If InStr(Ws.Name, strMonths(curMonth)) Then   ' << this will find the sheet with current month
    ' If InStr(Ws.Name, strMonths(curMonth)) Or InStr(Ws.Name, "FINRA") Then  ' << this will find sheets for this month or any including "FINRA"[/COLOR]
   
 If InStr(Ws.Name, strMonths(curMonth)) Or InStr(Ws.Name, strMonths(curMonth - 1)) Then [COLOR="#008000"]' << this will will find sheets for this month and last month[/COLOR]        
        MsgBox Ws.Name [COLOR="#008000"]' use your print sheet code here instead of alerting[/COLOR]
        
    End If
    
Next

End Sub