/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

5 votes292 views1 answer
Vba array worksheet.index
VBA
0 vote329 views1 answer
Add text to Shape VBA
VBA
0 vote297 views1 answer
Find days in month vba
VBA
1 vote261 views1 answer
Ms access open form to new record
VBA
0 vote311 views1 answer
Switch pictures with buttons vb.net
VBA
0 vote287 views1 answer
Vb test if reference is null
VBA
0 vote306 views1 answer
Vbnet line graph smooth lines
VBA
20 votes448 views1 answer
Excel vba recursive example factorial function
VBA
0 vote302 views1 answer
Excel vba show developer tab
VBA
1 vote300 views1 answer
Excel vba last row of range
VBA