VBA OLAP Help

[rivera24]

I am working on a project for my internship in which I am trying to automate a spreadsheet by writing a macro. What I have done is created a very small pivot table in the Homepage, with the report filter being "Resource Name." This report value gives the output at Cell "B8." From there, I used the split function because I want to create a sheet in a new tab that is named after the last name of the person selected in "B8" of "Homepage."

I then want to create a Pivot Table in the new sheet I just created. I should mention that the Pivot Table is drawing data from an OLAP Cube. I suspect the value I have in "TableDestination" is in the wrong notation. If someone could point me in the right direction, I would greatly appreciate it.

Sub ProjectLeadHome()
'
Dim Name As String
Dim i As Integer
Dim FullName As Variant

' Obtain name of chosen resource

Name = Worksheets("Homepage").Range("B8").Value
FullName = Split(Name, " ")

' Obtain last name from array

Dim LastNameNumber As Integer
LastNameNumber = UBound(FullName)

' Add worksheet with the last name of the resource

Sheets.Add(, Worksheets("Homepage")).Name = FullName(LastNameNumber)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("Connection"), Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:=Worksheets(FullName(LastNameNumber)), TableName:="Lead", _
DefaultVersion:=xlPivotTableVersion14

End Sub

[romperstomper]

The destination needs to be a specific cell not just a worksheet.