/VBA/Pivot data source not accepting table named range vba

Pivot data source not accepting table named range vba

'Check reference column and select the same number of rows in start and end columns
Sub selectByUsedRows(usedCol As String, selectStartCol As String, selectEndCol As String)
n = Range(usedCol & "1").End(xlDown).Row
Range(selectStartCol & "1:" & selectEndCol & n).Select
End Sub

'Dynamically select columns A to C with as many rows as are in A
Sub test()
refCol = "A"
selectStartCol = "A"
selectEndCol = "C"
selectByUsedRows refCol, selectStartCol, selectEndCol

'Code works until this point. There is now an active selection of A1:C8. 
'The following is hypothetical

Dim rngSelection As Range
Set rngSelection = ActiveSelection
Range(rngSourceData).CurrentRegion.Name = "rngSourceData"

Set objTable = Sheet5.PivotTableWizard

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    rngSourceData, Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet5!R1C4", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion14
End Sub

Source: stackoverflow.com

Similar codes

0 vote262 views1 answer
Excel vba populate array with cell values
VBA
0 vote298 views1 answer
Vba collection contains
VBA
1 vote273 views1 answer
Textbox find and replace vb
VBA
0 vote303 views1 answer
Delete all controls in an area vb
VBA
0 vote285 views1 answer
Convert unix to date vba
VBA
0 vote288 views1 answer
VBA Loop to range
VBA
5 votes303 views1 answer
Excel vba second byte from INTEGER
VBA
0 vote277 views1 answer
Vlookup columns not next to each other
VBA
0 vote298 views1 answer
Vbs exec hidden
VBA
0 vote347 views1 answer
Excel vba delete columns in range
VBA