Pivot Table; Adjusting Macro for added Rows 1

[LearningSql]

Hello All -- Thanks in advance. I've searched through the Help Menus / Web / Keyword Searches and have not found anything to assist. I may be searching by the wrong term. If there is another post on this just point me in the right direction.

I have Excel 97. Working on a Macro to create a Pivot Table. I have a weekly report running I need to create a Pivot Table on. Number of rows changes each week (usually adding another 15000 rows).

Is there a way to change my macro to adjust for the added rows? My macro is coded only for the first time taking rows 1 through (for example) 10000.

Property in Range method somehow I'm missing?
Hope this makes sense.
Thanks again.

 

[SkipVought]

Hi,

Some people select the enitre columns of the source data. I don't like to do that because, if you have dates, your Pivot Table will get royally screwed!

There is a way with VBA. Here's the method that I use regularly...

Your table should start in A1 and have contiguous data.
1. Select the entire table
2. Turn on your Macro recorder and then Name the selection Database, or whatever you want your source data Range Name to be. Turn off the macro recorder.
3. Go to the VB Editor (alt+F11) and modify the code as follows...

Sub ResizeSourceData()
    ActiveWorkbook.Names.Add _
        Name:="Database", _
        RefersTo:="=" & ActiveSheet.Name & "!" & Cells(1, 1).CurrentRegion.Address
End Sub

4. In the Project Explorer, right click the sheet object having the source data and paste this code...

Private Sub Worksheet_Change(ByVal Target As Range)
    ResizeSourceData
End Sub

The Worksheet_Change event fires the ResizeSourceData any time a change is made in the source data. You will see that if you add a row ro your table, Database adjusts to the added row.

VOLA! Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[LearningSql]

Kewl -- Star for You!

Thanks for the tip. I'll check it out and let you know if I have any problems -- although I doubt I will.

Thanks again.
P