Tying an Array of worksheets to a specific workbook

Question

The goal of my macro is from a large set of data, divide the data into separate workbooks for each product. After I paste only the specific data into the file, I want to copy 5 separate worksheets from the workbook with all of the data, each worksheet with a pivot table on them, set the data on the pivot table to the data on the spread sheet that I copied over previously. Then refresh the pivot tables.

My biggest question is how to I name an array of worksheets to a work book

Dim cell As Range, DataRng As Range
Dim curPath As String, curWB As String, newWB
Dim ArrayCM As Variant
Dim InxW As Long
Dim xTable As PivotTable

curPath = ActiveWorkbook.Path & "\"
curWB = ActiveWorkbook.Name
'Array of worksheets to be copied over for Company Manger
Set ArrayCM = curWB.Sheets(Array("CM YTD", "CM MTD", "CM Refurb", "TBM Local", "PSM")) 
--This Gives me an error

Because I'm going to creating several workbooks, I want to define my array to copy over from the master file to the new file. Help on this would be greatly appreciated.

You are confusing a String and a Workbook object. Set ArrayCM = Activeworkbook.Sheets(Array("CM YTD", "CM MTD", "CM Refurb", "TBM Local", "PSM")) — Gary's Student
I've tried both of these and neither worked. I want define the "Parent Workbook" so that I can copy worksheets to the children workbooks. — Bruce

Unknown Unknown · Accepted Answer · 2016-03-23T17:43:23

You can create a worksheet type var as an array, redim it and assign each element within the array to a Worksheet Object.

Sub manyWSs()
    Dim w As Long
    Dim cwb As Workbook
    Dim wss() As Worksheet

    Set cwb = ThisWorkbook

    With cwb.Worksheets(Array("Sheet1", "Sheet3", "Sheet5"))
        ReDim wss(1 To .Count)
        For w = 1 To .Count
            Debug.Print .Item(w).Name
            Set wss(w) = .Item(w)
        Next w
    End With

    For w = LBound(wss) To UBound(wss)
        Debug.Print wss(w).Cells(1, 1).Address(0, 0, external:=True)
    Next w
End Sub

The Worksheets(Array("Sheet1", "Sheet3", "Sheet5")) is not a true Worksheets collection so .Item(w) is used to pick out the pieces. Once each piece has been Set, all the properties, methods and members of a single Worksheet Object are available to each piece of the array with full parent Workbook object association.

Tying an Array of worksheets to a specific workbook

1 Answers