Creating pivot tables VBA

[mfizz]

I am using a range to create pivot tables. Unfortunately the length of the sheet changes hence the range and therefore "sourcedata" changes.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R2741C63", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("COUNT = 6")
        .Orientation = xlRowField
        .Position = 1
    End With

how can i tell excel to use the new range everytime using the r1c1 style.

 

[RoryA]

You can use CurrentRegion:

With Sheets("Sheet1")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'" & .Name & "'!" & .range("A1").Currentregion.address(referencestyle:=xlr1c1), Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion14
End With

 

[mfizz]

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _        "'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion14

Using this i get an unqualified reference error.

 

[RoryA]

That will happen if you simply remove bits of the code I posted!
You need the With ... and End With lines too.

 

[mfizz]

hahah sweet.. sorted mate thanks! you can understand i have been coding all day started lose pieces of my brain

 

[RoryA]

I know exactly how you feel!

 

[mfizz]

RoryA you wouldnt be able to help me with my other question would you

http://www.mrexcel.com/forum/excel-questions/748509-find-function.html#post3678965

 

[RoryA]

I've posted a suggestion.

 

[Richard Schollar]

you can understand i have been coding all day started lose pieces of my brain

I recommend outsourcing your coding needs to a generous friend - it worked for me!