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

 

[L. Howard]

Post the entire code you used with my suggestion.

Howard

 

[kgallego]

Post the entire code you used with my suggestion.

Howard

Howard,

I just copied and pasted your code. Was I supposed to alter or add to it?

Thanks,

Kelsey

 

[kgallego]

Thank you all for the help. I guess I haven't been very clear on what the goal that I'm trying to accomplish is.

The goal is to write a subroutine that will print multiple worksheets from an excel file to a pdf, and save that pdf file in the same folder as the source excel file is saved in.

So for example, the Excel File has 3 worksheets with data. With a click of a button I want all three of those worksheets printed to one PDF and saved.

I greatly appreciate the help.

Thanks,
Kelsey

 

[Rick Rothstein]

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

Sheets(Sheet2).Activate

The argument to the Sheets object is a text string, so unless Sheet2 is a String variable containing the name of the sheet, you need to quote the text argument...

Sheets("Sheet2").Activate

 

[kgallego]

Thanks Rick,

That kind makes sense to me, but I'm a little lost in the terminology and language (Object, String).

The three sheets are as follows:

Sheet11(AFE SUMMARY)
Sheet12(PERFORMANCE CURVES)
Sheet4(TOTAL FINAL COST)

Can you tell me where I'm going wrong with this code or what changes I need to make?

Option Explicit

Sub PDFprint()

Dim AFE As Worksheet
Dim PC As Worksheet
Dim TFC As Worksheet

Set AFE = Sheet11
Set PC = Sheet12
Set TFC = Sheet4

ThisWorkbook.Sheets(Array(AFE, PC,TFC)).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\tempo.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True


End Sub

Thanks,

Kelsey

 

[kgallego]

Also,

That code I have doesn't seem to have any direction where the file is saved to. I'd like to to automatically save the PDF file in the same folder as the Excel file is in. Any thought's on how to do that?

Thanks again,
Kelsey

 

[L. Howard]

Howard,

I just copied and pasted your code. Was I supposed to alter or add to it?

Thanks,

Kelsey

Hi Kelsey,

The code copied to a standard module and run from, say, Sheet4 in the workbook should return a "Hello Sheet4" to A2 on the sheets named Sheet1, Sheet2, Sheet3.

If run from Sheet 2, then a "Hello Sheet2" would appear on Sheet1, Sheet2, Sheet3.

If there are NO sheets named Sheet1, Sheet2, Sheet3, (as named in the array) then you would get the error you mentioned.

Change the names in the array to match exactly your sheets you want code action on and see if that does it.

Howard

 

[kgallego]

Hi Kelsey,

The code copied to a standard module and run from, say, Sheet4 in the workbook should return a "Hello Sheet4" to A2 on the sheets named Sheet1, Sheet2, Sheet3.

If run from Sheet 2, then a "Hello Sheet2" would appear on Sheet1, Sheet2, Sheet3.

If there are NO sheets named Sheet1, Sheet2, Sheet3, (as named in the array) then you would get the error you mentioned.

Change the names in the array to match exactly your sheets you want code action on and see if that does it.

Howard

Thanks - but I can't refer to the sheets by their names because people change the names of the worksheet and then the code doesn't work. I have to address them as sheet1, sheet2, etc. referring to their location (forgive my terminology - I'm no expert).

 

[kgallego]

All,

I was able to almost get what I needed with the code below, however, I still have a couple issues:

1. Is there a way to "unselect" or "deselect" these worksheets at the end of the Sub? -This is going to cause me problems in the future.

2. It appears that the way the Array is selecting sheets is by how they are listed from left to right. If someone moves a sheet around in my workbook then the code becomes null and void. Is there a way I can tell it to select this sheets WITHOUT using the the sheet name (because people will change it) and without using it's location from left to right?

3. When it creates the PDF, it doesn't print it in the order that I listed (2,11,12,3,4,5,6), it changes it and prints it in the numerical order (2,3,4,5,6,11,12). Is there any way that I can lock the order as I have written it in the code?

Sub PDFprint()


ThisWorkbook.Sheets(Array(2, 11, 12, 3, 4, 5, 6)).Select


ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="Testthismother****er", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False



End Sub



Thanks,
Kelsey

 

[L. Howard]

Thanks - but I can't refer to the sheets by their names because people change the names of the worksheet and then the code doesn't work. I have to address them as sheet1, sheet2, etc. referring to their location (forgive my terminology - I'm no expert).

Maybe try it like this example I found.

Howard

Sub Many_Sheets_Array_2()
    Dim ArrayOne() As String
    
    Dim i As Long
    ReDim ArrayOne(1 To Sheets.Count)
   
    For i = 1 To Sheets.Count
    
      With Sheets(i)
        'code you want to do for each sheet here
        .Range("A6").Value = "Hello and stuff...  " & ActiveSheet.Name
   
      End With
      
    Next
   
End Sub