VBA name within range to select worksheet

[EMcK01]

Hi,

I'm sure its something obvious but can't see it.

I want my code to select the sheet to use to create a Pivot Table based on the name thats in the Read Me worksheet, as this sheet name will vary.

The below works fine as a defined name

    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotCache As PivotCache
    Dim myPivotTable As PivotTable
    
    'declare variables to hold source and destination range
    Dim mySourceRange As Range
    Dim myDestinationRange As Range
 
    'identify source and destination worksheets. Add destination worksheet
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Sheet1")
        Set myDestinationWorksheet = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
        myDestinationWorksheet.Name = "Temp"
    End With

I have tried changing

Set mySourceWorksheet = .Worksheets("Sheet1")

to

Set mySourceWorksheet = Sheets("READ ME").Range("B5")

but am getting a Type Mismatch

 

[Fluff]

Try

Set mySourceWorksheet = Sheets(Sheets("READ ME").Range("B5").Value)

 

[EMcK01]

Try

Set mySourceWorksheet = Sheets(Sheets("READ ME").Range("B5").Value)

Perfect, thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[EMcK01]

Fluff kindly helped and solved my issue last week, however I've realised that I want to create the pivot table for multiple sheets in the workbook.

I've listed the sheet names in column P on my "read me" sheet and was intending to loop through the list to create a pivot table for the sheets listed.

I tried changing the line

Set mySourceWorksheet = Sheets(Sheets("READ ME").Range("B5").Value)

to

Set mySourceWorksheet = Sheets(Sheets("READ ME").Range(Cells(c,16)).Value)

but I'm now getting a run time error message.

any suggestions?

 

[Fluff]

Try

Set mySourceWorksheet = Sheets(Sheets("READ ME").Range("P"&c).Value)

 

[EMcK01]

Try

Set mySourceWorksheet = Sheets(Sheets("READ ME").Range("P"&c).Value)

Of course it was, thanks Fluff, quick response is much appreciated

 

[Fluff]

Glad to help & thanks for the feedback.