Changing Source Data Using Macro

[therrma2]

I have a file where I add new source data every week. The columns are always the same, but the number of rows changes everytime I update the data. I'm trying to create a Macro so that once I copy and paste the new data in, I can simply run the macro and the source data will expand to include the new rows. I'm fairly new to VBA.

Basically, I need the Macro to identify what the last row of data is, and then change the source data on the pivot table to include it.

Thanks for your help.

 

[jim may]

Assign a DYNAMIC Range Name to the pasted in data.

RangeName: MyData
RefersTo (box): =OFFSET(ANCHOR_CELL, 0, 0, COUNTA(RANGE),0)
sample =OFFSET($A$2, 0, 0, Counta(B:B)-1, 0) ' -1 allow for single-row header

 

[jim may]

Mistake..
sample =OFFSET($A$2, 0, 0, Counta(B:B)-1, 0)

s/b
sample =OFFSET($A$2, 0, 0, Counta(B:B)-1, 7) << Allow for Columns A-G

 

[therrma2]

Thanks Jim. I was finally able to get it to work. For anyone else with a similar problem, here is my code:

Dim MyData As Range
Set MyData = Sheets("Data Table- Do Not Read").Range("OFFSET($A$7,0,0, COUNTA(D:D),15)")

'
Sheets("Division Summary ").Select
Range("D15").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
MyData _
, Version:=xlPivotTableVersion10)

 

[jim may]

These 2 lines will set your pasted in range dynamiclly, and name Mydata


ActiveSheet.Names.Add Name:="MyData", RefersTo:="=OFFSET($A$7,0,0,COUNTA(D:D),15)"
Set Mydata = Range("MyData")