VBA - Assign multiple worksheets as one variable

[kgallego]

Hello all,

Is there a way to assign multiple worksheets as one variable? Example:

Dim wsA as Worksheet
Dim wsB as Worksheet
Dim wsC as Worksheet

????Set wsABC = wsA & wsB & wsC????

Thanks,

Kelsey

 

[Logit]

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Try this:

[COLOR=#101094]Dim[/COLOR][COLOR=#303336] wSheet() [/COLOR][COLOR=#101094]as[/COLOR][COLOR=#303336] Worksheet
wSheet [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Array[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Sheet1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Sheet2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Sheet3[/COLOR][COLOR=#303336])
Set wsABC = wSheet[/COLOR]

</code>

 

[kgallego]

Thanks - didn't quite work for what I was trying to do. Thanks anyways.

 

[Logit]

Ok, this works for the intended project here. Hopefully you can adapt it for your use.

Also, you can download the workbook example here ---> https://www.amazon.com/clouddrive/s...hqr7wkrcdjniG6XDaJ?ref_=cd_ph_share_link_copy

Option ExplicitSub test2()


Dim wsABC As Variant
Dim wsABCs As Variant
wsABCs = Array("Sheet1", "Sheet2", "Sheet3")


For Each wsABC In wsABCs
    Sheets(wsABC).Select
    'your code here
    With Sheets(wsABC)
        .Range("A1").Value = "HI"
    End With
    MsgBox ActiveSheet.Name 'test loop
Next wsABC
End Sub

 

[kgallego]

Thanks for the code. The purpose of this is to be able to print several worksheets as a single PDF, so my thought process was to define variables to each of the worksheets via:

Dim wsA as Worksheet
Set wsA = Sheet1
.....

And then combine all the variables (wsA, wsB, wsC) into one variable (wsABC), where I could then print all as PDF's with the code:

If myFile <> "False" Then
wsABC.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
End If


exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler




Does this make any sense, or am I going about this the wrong way?

Thanks,
Kelsey

 

[L. Howard]

Another way.

Howard

Sub Many_Sheets_Array()
Dim i As Long
Dim MyArr As Variant

MyArr = Array("Sheet1", "Sheet2", "Sheet3")
                
Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)
  
   With Sheets(MyArr(i))
   
    .Range("A2").Value = "Hello " & ActiveSheet.Name
     
  End With
  
Next 'i

Application.ScreenUpdating = True
End Sub

 

[Logit]

Option Explicit

Sub Macro1()


   Sheets("Sheet1").Activate
   ActiveSheet.UsedRange.Select
   Sheets("Sheet2").Activate
   ActiveSheet.UsedRange.Select
   Sheets("Sheet3").Activate
   ActiveSheet.UsedRange.Select


   ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "C:\Users\My\Desktop\Test.pdf", Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
      True
End Sub

 

[kgallego]

Thanks L. Howard,

But for some reason this code wouldn't run. I hit an error on the "With Sheets(myArr(i))" line.

 

[kgallego]

Option Explicit

Sub Macro1()


   Sheets("Sheet1").Activate
   ActiveSheet.UsedRange.Select
   Sheets("Sheet2").Activate
   ActiveSheet.UsedRange.Select
   Sheets("Sheet3").Activate
   ActiveSheet.UsedRange.Select


   ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "C:\Users\My\Desktop\Test.pdf", Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
      True
End Sub

I think this is right on path for what I'm trying to do, but it's not working for some reason. I made the following changes and my code is below. I'm getting an error on the first line.
Note that I removed the quotations for the sheets. People change the sheet names, so I need to address the sheets by the sheet number and not the name.

The error says "Type Mismatch" and it's on the first line of code.?.?

Sub Macro1()




Sheets(Sheet2).Activate
ActiveSheet.UsedRange.Select
Sheets(Sheet6).Activate
ActiveSheet.UsedRange.Select
Sheets(Sheet9).Activate
ActiveSheet.UsedRange.Select


ThisWorkbook.Sheets(Array(Sheet2, Sheet6, Sheet9)).Select
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\My\Desktop\Test.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub





Thanks,
Kelsey

 

[kgallego]

I also tried this (below). Same error:

Sub Macro1()


Dim DT As Worksheet
Dim CT As Worksheet
Dim PT As Worksheet


Set DT = Sheet2
Set CT = Sheet6
Set PT = Sheet9




Sheets(DT).Activate
ActiveSheet.UsedRange.Select
Sheets(CT).Activate
ActiveSheet.UsedRange.Select
Sheets(PT).Activate
ActiveSheet.UsedRange.Select


ThisWorkbook.Sheets(Array(DT, CT, PT)).Select
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\My\Desktop\Test.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub

Thanks,
Kelsey