Pivot Chart help needed

[MartinL]

I am trying to create a chart on each sheet.
my method was to use pivot table/chart and then whan the sheet was copied the table/chart would get updated with the new data.
Not So

so I am now trying the same thing but on a button. I recorded a macro and am now trying to modify it by changing the hard coded bits to variables.

I am hitting a problem here - right at the top (

stepping through the code [F8] it fails at:

    WB.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'" + WS + "'!R6C71:R" + LR + "C74").CreatePivotTable TableDestination:= _
        "'[" + WB + "]" + WS + "'!R111C11", TableName:= _
        WS + " Chart", DefaultVersion:=xlPivotTableVersion10

this is what i have so far (plus the rest of the macro)

Dim WS As Worksheet
    Dim WB As Workbook
    Dim LR As Long
    Dim CN As String
    
    ActiveSheet.Unprotect
    With Application
        .Calculation = xlCalculationManual
    End With

    Range("A1").Select
    With ActiveSheet
        LR = .Cells(Rows.Count, "BS").End(xlUp).Row
    End With
'
    'ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'JS PREP WK (42)'!R6C71:R93C74").CreatePivotTable TableDestination:= _
        "'[Auto board 131009 (VERSION 7a).xls]JS PREP WK (42)'!R111C11", TableName:= _
        "PivotTable3", DefaultVersion:=xlPivotTableVersion10
    
    WB.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'" + WS + "'!R6C71:R" + LR + "C74").CreatePivotTable TableDestination:= _
        "'[" + WB + "]" + WS + "'!R111C11", TableName:= _
        WS + " Chart", DefaultVersion:=xlPivotTableVersion10
    
    CN = WS + " Chart"

any ideas what im doing wrong...?

Martin

 

[MartinL]

Oh 14 views and no comments

maybe someone can suggest a different method of achieving what i need to do...