Damn VBA Macro!!! (Help Please)

[bassist_25]

I'm trying to write a macro for Excel in which I create a PivotTable from a set of source data. The only problem is the source data will be continually changing, so I have to let the macro know to always select the usedrange. Unforunatley, the way the PivotTable selects the information isn't based on a range, but rather rows and columns. I tried creating a variable that counted the number of rows, but I don't think that would work. I was wondering if anyone could give me a hint on how to make this work. I originally recorded the macro and thought I could just insert the usedrange/variable in it, but it didn't turn out to be that simple. Here's a copy of my code:


Sub PT()
'
' PT Macro
' Macro recorded 4/20/2006 by prainey
'
Dim p As Integer
p = Sheets("Sales and Profits").UsedRange.Rows.Count + 1

'
ActiveSheet.UsedRange.Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Sales and Profits'!R1C1:R8C6").CreatePivotTable TableDestination:= _
"'[project.xls]Table and Chart'!R1C1", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Product Name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Profit"), "Sum of Profit", xlSum
Sheets("Sales and Profits").Select
End Sub

Any help would be appreciated.

P.s. I bolded the part of the code that is the source range for the PivotTable.

[DirtySanchez]

I would love to help but can't, so I will at least cheer you up with a story of how low tech I am. I saw a truck 4 sale that I really liked. I stopped looked at it and the owners # was on the windshield. I have a cell phone
and only know how to use the camera and make calls. I do not know how to enter #'s and save them. I had no pen on me so I took a picture of the windshield. I showed my wife (mistake) and haven't heard the end of it since. So it sounds like you are Quite technologically advanced, maybe I'll come to a BadDaze show so you can help me use my cell phone.

P.S. good luck with the pivot table macro with variables and if all else fails use duct tape, that stuff is amazing.

[bassist_25]

*LOL* Not a problem. I actually got it to work. I just had to place my &'s and paranthesis in the rights places. Now I'm trying to get a macro to work that runs a solver for a linear program.

I'll definitely help you out on the cell phone, though.

[ToonaRockGuy]

Actually, Paul, you need to focus on a macro that can merge the outstanding properties of Beans, oLd sKool, and Snickers with the George Foreman Grill.

[tornandfrayed]

No really Paul what do you do for fun!

[bassist_25]

Actually, Paul, you need to focus on a macro that can merge the outstanding properties of Beans, oLd sKool, and Snickers with the George Foreman Grill.

I'm afraid that Visual Basic is just too limited of a language to be able to program something so capital. I'd need to at least program that in Cobol.

No really Paul what do you do for fun!

That's just freaky.