View Full Version : [SOLVED:] Excel 2013>VBA>Sheets>Array
aworthey
07-07-2016, 11:36 AM
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!
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
07-07-2016, 02:40 PM
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!
"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
07-12-2016, 02:51 PM
SamT,
Thank you very much for taking the time to explain this! It is very helpful. I appreciate it.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.