pivot data source not accepting table named range vba

The solution for “pivot data source not accepting table named range vba” can be found here. The following code will assist you in solving the problem.

‘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 _
End Sub

Thank you for using DeclareCode; We hope you were able to resolve the issue.

More questions on [categories-list]

Similar Posts