Reputation: 7
I have a problem in creating a .pdf file out of different not predefined numbers of sheets.
Logic: Press Button1 - creates a new sheet (Employee x) e.g. you create 3 different sheets for your employees.
So, I want to create a .pdf file only out of these 3 sheets.
For-Loop gives me this String: "Worker1", "Worker2", "Worker3"
If I put this String (mystring) into :
Sheets(Array(**mystring**)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
openafterpublish:=False, ignoreprintareas:=False
I get the Error: Run-time error '9': Subscript out of range.
Otherwise, If I insert the strings manually it works. But it's no predefined how many employees you have and what their name is.
Sheets(Array("Worker1", "Worker2", "Worker3", ... ,"Workerx")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
openafterpublish:=False, ignoreprintareas:=False
Some one any idea how to fix this?
Upvotes: 0
Views: 407
Reputation: 166980
If you create the string as "Worker1,Worker2,Worker3" you can use Split(myString, ",")
to create the array.
Something like this:
Option Explicit
Private Sub CommandButton1_Click()
Dim stringname As String, wb As Workbook, ws As Worksheet, i As Long, sep As String
Set wb = ThisWorkbook 'or ActiveWorkbook
Set ws = wb.Worksheets("MitarbeiterListe")
For i = 6 To 106
If ws.Range("B" & i) <> "" Then
stringname = stringname & sep & ws.Range("B" & i) & " " & ws.Range("C" & i)
sep = "," 'populate separator after first item
End If
Next i
MsgBox stringname
ThisWorkbook.Sheets(Split(stringname, ",")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
openafterpublish:=False, ignoreprintareas:=False
MsgBox "All PDF's have been successfully exported."
End Sub
Upvotes: 1
Reputation: 7
**FULLCODE**
Private Sub CommandButton1_Click()
Dim forename As String
Dim lastname As String
Dim fullname As String
Dim stringname As String
For i = 0 To 100
If Sheets("MitarbeiterListe").Range("B" & 6 + i) <> "" Then
forename = Sheets("MitarbeiterListe").Range("B" & 6 + i)
lastname = Sheets("MitarbeiterListe").Range("C" & 6 + i)
fullname = Chr(34) & forename & " " & lastname & Chr(34)
stringname = stringname + fullname & ", "
End If
Next i
stringname = Left(stringname, Len(stringname) - 2)
MsgBox stringname
Sheets(Array(stringname)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", _
openafterpublish:=False, ignoreprintareas:=False
MsgBox "All PDF's have been successfully exported."
End Sub
Upvotes: 0