Make my VBA code smarter

[Arie Bos]

I have 70 sheets in a workbook (Named LP01-LP70). This amount may increase to 150.

Every month, I have to save these sheets as pdf's in a folder name that matches the current month: 17_08.

Now, I have the following line-by-line instruction, in which I manually replace the month number every time I do this.
Each line produces a pdf file with the name: "BBOT1 [17_08] LP33.pdf"

Sheets("LP01").ExportAsFixedFormat 0, "C:\Users\.....\Monthly Reports\17_08" & "BBOT1" & " [" & Sheets("START").Range("SelectedMonth") & "] " & Sheets("LP01").Range("Z11") & ".pdf"
Sheets("LP02").ExportAsFixedFormat 0, "C:\Users\.....\Monthly Reports\17_08" & "BBOT1" & " [" & Sheets("START").Range("SelectedMonth") & "] " & Sheets("LP02").Range("Z11")& ".pdf"
Sheets("LP03").ExportAsFixedFormat 0, "C:\Users\Arie\Dropbox\BCM\....

etc., 70 lines doing this same thing.

How can I make this smarter?

 

[stumac]

If you can use logic to identify the sheets you need to export then loop through them, build the filename and export them within the loop... e.g. in the example below I am saying loop through all sheets that aren't called "START"...

Sub expsheets()
Dim Fname As String
Dim Sht As Worksheet
    For Each sht In ActiveWorkbook.Sheets
        If sht.Name <> "START" Then 'Exclude the sheet called START
    
        Fname = "C:\Users\.....\Monthly Reports\17_08" & "BBOT1" & " [" & Sheets("START").Range("SelectedMonth") & "] " & sht.Range("Z11") & ".pdf"
        sht.ExportAsFixedFormat 0, Fname
    
        End If
    Next sht
End Sub

 

[Arie Bos]

Thank you Stumac,
this is exactly want i wanted to accomplish.

Apart from "START", there are 3 more sheets that are not named "LPxx", so, can this line be adjusted to: If sht.name <> "START" or "NOTES" or "..." etc.?

 

[Peter_SSs]

I've also tried to build the current month into the code though I'm wondering if that is being duplicated in 'START' -> Range("SelectedMonth")
The sheet name may also be duplicated in cell Z11 on each sheet? If so, the blue part in the code could be replaced with ws.Name

Untested, and of course you would need to complete the "..." part of the file path string.

Sub Export_Sheets()
  Dim ws As Worksheet
  Dim CurrMonth As String, SelMnth As String, Fname As String
  
  Const NameBase As String = "C:\Users\.....\Monthly Reports\@\BBOT1 [^] #.pdf"

  CurrMonth = Format(Date, "yy_mm")
  SelMnth = Sheets("START").Range("SelectedMonth").Value
  For Each ws In Worksheets
    If ws.Name Like "LP*" Then '<- Exclude any sheets that don't start with "LP"
      Fname = Replace(Replace(Replace(NameBase, "@", CurrMonth), "^", SelMnth), "#", ws.Range("Z11").Value)
      ws.ExportAsFixedFormat 0, Fname
    End If
  Next ws
End Sub

 

[stumac]

No problem

If the all sheets beginning with LP should be exported then use that to define what sheets to export:

Sub expsheets()
Dim Fname As String
Dim Sht As Worksheet
    For Each Sht In ActiveWorkbook.Sheets
        If UCase(Left(Sht.Name, 2)) = "LP" Then 'Only include sheets beginning LP
    
        Fname = "C:\Users\.....\Monthly Reports\17_08" & "BBOT1" & " [" & Sheets("START").Range("SelectedMonth") & "] " & Sht.Range("Z11") & ".pdf"
        Sht.ExportAsFixedFormat 0, Fname
    
        End If
    Next Sht
End Sub

 

[Arie Bos]

Thank you both, Peter and Stumac,

I will test it later today and get back to you. Great help!
Arie