Excel Print sheets from array

Question

I want to select a array of sheets using the Sheets(Array()) method. The sheets I want to select are named in the cells of my workheet Printlijst. The sheetnames are listed form column D to K.

Not all cells are filled so if I use the folowing function

This is what the sheet looks like: https://i.stack.imgur.com/uJqZc.jpg

And this is the code:

Sub PDF_maken()
Dim ws As Worksheet
Dim LR As Long
Dim r As Range
Dim Mypath As String
Dim strarray As String


Set ws = ActiveWorkbook.Worksheets("Printlijst")
LR = ws.Cells(Rows.Count, 1).End(xlUp).Row

For Each r In ws.Range("B20:B20").Cells

If Not IsEmpty("B" & r.Row) Then
Mypath = ws.Range("B" & r.Row).Text

colCheck = 4
Do Until Cells(r.Row, colCheck) = ""
    strarray = strarray & IIf(colCheck > 4, ",", "") & Cells(r.Row, colCheck).Value
colCheck = colCheck + 1
Loop

ActiveWorkbook.Sheets(strarray).Select
    ActiveWorkbook.SelectedSheets.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Mypath & ws.Range("C" & r.Row).Text & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False


End If
Next r

End Sub

The errors happens when more than one sheet must be selected When I use the errorcheck strarray is "2450,2451,2452,2453,2454,2455,2456,2457"

score 0 · Accepted Answer · edited May 23 '17 at 12:22

0

You cannot use strarray in ActiveWorkbook.Sheets(strarray). It is expecting a single sheet name or a collection of sheets in array.

Is this what you are trying?

'
'~~> Rest of your code
'

Dim strarray  As String
Dim MyAr As Variant

Do Until Cells(r.Row, colCheck) = ""
    strarray = strarray & IIf(colCheck > 4, ",", "") & Cells(r.Row, colCheck).Value
    colCheck = colCheck + 1
Loop

If InStr(1, strarray, ",") Then
    MyAr = Split(strarray, ",") '<~~ This is where we are creating an actual array

    ActiveWorkbook.Sheets(MyAr).Select
Else
    ActiveWorkbook.Sheets(strarray).Select
End If

'
'~~> Rest of your code
'

NOTE: BTW, you should avoid using .Select and directly perform the operation on those sheet(s). You may want to see How to avoid using Select in Excel VBA macros

edited May 23 '17 at 12:22

Community

1
1

answered Jul 28 '16 at 13:32

Siddharth Rout

147,039
17
206
250

Thanks! It seems to work, the sheets stored in the array are selected. Only problem now is that when multiple sheets are selected the exporting command does not work. When only one sheet must be printed it does work :) – user4373888 Jul 28 '16 at 14:51
When you are exporting multiple sheets then you can use `Selection` after the `.Select`. However ensure that all those sheets are unhidden else the `.Select` command will give an error. `Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename......` – Siddharth Rout Jul 28 '16 at 15:00
What is the error that you are getting? I may not be able to answer in the next 12 hours as I am about to hit the sack. But i will look at it in the morning when I wake up :) – Siddharth Rout Jul 28 '16 at 17:07
property or method not supported by the object – user4373888 Jul 28 '16 at 20:45
When I use the following code to print it does work however `ActiveWorkbook.Sheets(MyAr).Select ActiveWorkbook.Sheets(ws.Range("D" & r.Row).Text).Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False ` – user4373888 Jul 28 '16 at 20:45
After the line `ActiveWorkbook.Sheets(strarray).Select` in the next line type this and then try. `Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= Mypath & ws.Range("C" & r.Row).Text & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False` Ensure that `Mypath & ws.Range("C" & r.Row).Text` has a valid value – Siddharth Rout Jul 29 '16 at 03:47
I think there is something wrong with te path. It is a bit long. We I shorten the pathname it does work. Thanks a lot – user4373888 Jul 29 '16 at 06:38

Excel Print sheets from array

1 Answers1