EOF in Excel VBA

D

Daniel

Hi Everyone,

I have recorded a macro that will pivot some data. Each
month I get a fresh batch of data that needs to be
pivoted in the same way. Each month though the amount of
records increases, although the format stays the same, so
at the moment I go into the VBA code and change the
number of rows that excel with pivot tom allow for the
new number of records. Is it possible to uise some code
code to tell excel to look untill there are no more
records to know how much data to pivot.

I don't know a great deal about VBA just stuff I've
picked up along the way so I won't kow where to put it if
there is some code available: here is a sample of my code:

Range("A1").Select
ActiveWorkbook.PivotCaches.Add
(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R8830C23").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable2",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
 
D

Dave Peterson

If you know the number of columns (23=column W) and can pick out a column that
always has an entry (I used column A in my example):

dim myPTRng as range
with worksheets("Sheet1")
set myptrng = .range("a1:W" & .cells(.rows.count,"A").end(xlup).row)
end with

then later...

,sourcedata:=myptrng.address(external:=true), ...

If the data can change after you create the pivottable, you may want to look at
using a dynamic range. You can see some instructions at Debra Dalgleish's site:

http://www.contextures.com/xlNames01.html#Dynamic
 
Top