MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
VBA bit,
Range("A3:I256")
This works fine when the pivot table has a data source of A3 to I256, problem is how do i get this to change.
i have used rows.count to populate a variable and find the end of the data not sure how i can change the 256 to be rows.count.
anyone help.
What is the correct syntax.
use offset to give you a variable range.
managed to get it to work, create a text variable, and concatenate the text/variable to create the range variable.
Seems to work.
using "indirect?"
I find that instead of using a string for the Range reference it's easier to use cells eg; this code colours cells A1:J1 in yellow, you can easily use any integer count to modify the range through the Cells(rows,columns)
[code]
Public Sub tester()
Dim myRange As Range
Set myRange = ActiveWorkbook.ActiveSheet.Range(Cells(1, 1), Cells(1, 10))
myRange.Interior.ColorIndex = 6
End Sub
[/code]
rowsfd = Worksheets("FD").UsedRange.rows.Count
j = "Z" & rowsfd
pivotfd = "A3:" & j
Range(pivotfd).Select
Next query
I am trying to create a pivot table. This doesn't work, but if i define a named range in Excel and use that as the range for sourcedata it works. So how am i using Sourcedata incorrectly?
Sub pivotroof()
Dim PC As PivotCache
Dim PT As PivotTable
Set flooringdata = Worksheets("FD").Range("a3:z100")
Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=(flooringdata))
Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PC, Tabledestination:="")
I think that the sourcedata variable is string only so just use the "FD!A3:Z100" string instead