Excel 2013>VBA>Sheets>Array

[aworthey]

I've seen several examples of using Array nested within Sheets in order to work with multiple sheets. I always receive an error that Object doesn't support this property or method. Is there something I'm overlooking? Or is there another method? I am trying to save multiple worksheets within a single PDF.


Option Explicit
Sub Button2()
Application.ScreenUpdating = False

Dim strFilename As String
Dim rngRange As Range
Dim konumber As String

konumber = Environ$("UserName")
Set rngRange = ThisWorkbook.Sheets("CostWorksheet").Range("D5")
strFilename = rngRange.Value & Format(Now(), "mmddyyyy hhmm")

Sheets("temp").Visible = True

Sheets(Array("temp", "CostWorksheet")).Activate

With Sheets(Array("temp", "CostWorksheet")).PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Sheets("CostWorksheet").EnableCalculation = False
Sheets("CostWorksheet").EnableCalculation = True

Sheets("CostWorksheet").Calculate

Sheets(Array("temp", "CostWorksheet")).ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Sheets("temp").Visible = False

ActiveCell.Offset(1000, 1000).Activate

Application.ScreenUpdating = True
End Sub


Thanks!

[SamT]

Dim mySheets as Variant
mySheets = Array("temp", "CostWorksheet")

'More code here

For i = 0 to 1
With Sheets(mySheets(i))
With .PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With 'Page set up

If mySheets(i) = "CostWorksheet" Then .Calculate

.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With 'Sheet i
Next i

'More code here

[aworthey]

Hello SamT,

Thank you very much for your response!

I just got it to work right before you posted. Here's what I did:


Option Explicit
Sub Button2()
Application.ScreenUpdating = False

Dim strFilename As String
Dim rngRange As Range
Dim konumber As String

konumber = Environ$("UserName")
Set rngRange = ThisWorkbook.Sheets("CostWorksheet").Range("D5")
strFilename = rngRange.Value & Format(Now(), "mmddyyyy hhmm")

ThisWorkbook.Sheets("temp").Visible = True

ThisWorkbook.Sheets(Array("temp", "CostWorksheet")).Select

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Sheets("CostWorksheet").EnableCalculation = False
Sheets("CostWorksheet").EnableCalculation = True

Sheets("CostWorksheet").Calculate

ThisWorkbook.Sheets(Array("temp", "CostWorksheet")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\" & konumber & "\Desktop\" & strFilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

ThisWorkbook.Sheets("temp").Visible = False

ThisWorkbook.Sheets("CostWorksheet").Select

ActiveCell.Offset(1000, 1000).Activate

Application.ScreenUpdating = True
End Sub


I know this is likely very inefficient, but I've only been using VBA for a few months.

I love your solution--but I don't understand exactly how it works. I've noticed solutions for various tasks recently using "i" but I get lost when trying to follow the logic. Is there a resource you recommend that explains it?

Thanks, again!

[SamT]

"i" is just a "Loop" counter in this case. Inside the array Parentheses, it is an index number


For i = 0 to Ubound(myArray)
With myArray(i)
do stuff
End With
Next i

You can Step i by more than just one and in the decreasing direction

For i = 100 to 2 Step -2
Blah blah with index 100, then with 98, then 96, 94, etc.
Next 'i

The use of "i" is pretty much a programming industry standard for use in loops and for other indexing uses. When you see any of three lower case letters "i," "j," and "k," you can bet they are just simple counters and indices. BTW, never use Lower Case Ell ("l") or uppercase Oh ("O".) They look like one and zero


For i = 1 to 100
For j = 1 to 100
For k = 1 to 100
X = i + j + k
Next k
Next j
Next i
In VBA for Excel only, I sometimes use "r" and "c" for Row and Column indices.


Unless otherwise stated, Arrays indices start at 0, so

For i = 0 To 1
With Sheets(mySheets(i))
Means: With the Sheet named From the first array item to the second array item. The first code example above means from the zeroth index number to the last, (UBound.) No matter how many array items there are, the first array item's index is always LBound(Array), and the last item's index is always UBound(Array). "L" for lower and "U" for upper Boundary.

[aworthey]

SamT,

Thank you very much for taking the time to explain this! It is very helpful. I appreciate it.

[snb]

You might try:


Sub M_snb()
With Sheets(Array("temp", "CostWorksheet"))
.Visible = True
.ExportAsFixedFormat 0, CreateObject("wscript.shell").specialfolders(10) & "\" & Sheets("CostWorksheet").Cells(5, 4) & Format(Now(), "mmddyyyy_hhmm") & ".pdf"
End With

Sheets("temp").Visible = False
End Sub