plz help with pivot table question

[singals]

Hello,

I need a serious help from you guys.

Here is my question.

I have a worksheeet which contains 3 years of data(from mar 2003 to till now) and data keep adding every month to the same sheet. Rest of the worksheets of this workbook has pivot tables and graph charts.

right now, I manually go to pivot table worksheet and select 12 months of data to show in pivot table and in charts.

I wanted to do all this autonatic. so when i have month of nov data in data sheet so i don't have to go pivot table and unselect nov, 2005 and select nov, 2006 to show the this month in pivot table and charts.

my goal is to show 12 months of data in pivot and charts even though data sheet has 3 years of data. how can i do that.?

I would really appericate your help. Can't thank you enough

Ps: i have a macro in place which refresh all the pivot table and graph and based on the new month i added. but then it show's 13 months of data instead of 12. I am attching a macro code for you to review.

Sub getTomdata()

' Pull the Tom sheet
' Macro recorded 9/19/2006 by

Workbooks.Open Filename:= _
"C:\Documents and Settings\singalt\Desktop\Book1.xls"
Rows("1:1").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows("combined 9-7-06 v1 (version 2).xls").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("a1").Select
Selection.PasteSpecial Paste:=xlValues

End Sub
Sub FillDates()
r = ActiveSheet.UsedRange.Rows.Count
For aj = 2 To r ' i assume that the first row is header info

If IsEmpty(Range("AJ" & aj)) Then
' only fill out blanks
Range("AJ" & aj).Value = "=+concatenate(text(today(),""yy""),text(today(),""mm""))"

' month
Range("AK" & aj).Value = Year(Now()) ' year
Range("Al" & aj).Value = "Q" + Chr(48 + Choose(Month(Now), 1, 2, 2, 2, 3, 3, 3, 4,

4, 4, 1, 1)) ' year
End If

Next aj
Columns("aj:aj").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub


Sub Getlaborrate()
'
' lookup filed

Range("AQ1").Select
ActiveCell.Value = "=+CountA(b:b)"
n = ActiveCell.Value
ActiveCell.Value = "=+CountA(am:am)"
M = ActiveCell.Value
J = n - M

Range("AM1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("a1").Select
For i = i To J - 1
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-30],'Labor Rates'!R2C1:R100C2,2,FALSE)"
ActiveCell.Offset(1, 0).Range("a1").Select
Next i


End Sub
Sub Getcostdata()
'
' take a repair cost and multipy by total hours

Range("as1").Select
ActiveCell.Value = "=+CountA(b:b)"
n = ActiveCell.Value
Range("aR1").Select
ActiveCell.Value = "=+CountA(an:an)"
M = ActiveCell.Value
J = n - M

Range("An1").Select
Selection.End(xlDown).Select
Selection.Copy
For i = i To J - 1
ActiveCell.Offset(1, 0).Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Next i

End Sub

Sub TotalSumData()
'
' Sumsup the total

Range("as1").Select
ActiveCell.Value = "=+CountA(b:b)"
n = ActiveCell.Value
Range("aR1").Select
ActiveCell.Value = "=+CountA(ao:ao)"
M = ActiveCell.Value
J = n - M

Range("Ao1").Select
Selection.End(xlDown).Select
Selection.Copy
For i = i To J - 1
ActiveCell.Offset(1, 0).Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Next i

Range("Aq1:as1").Select
Selection.ClearContents
Range("as1").Select


End Sub
Sub RefreshAll()
'Macro to refresh all pivot tables and charts in workbook

Dim ws As Worksheet
Dim pt As PivotTable
'
'Step through each worksheet in the active workbook and refresh any pivot tables found.
'
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws
'
'Inform the user when report refresh completes.
'
MsgBox "Refresh Complete!", vbInformation, "Refresh Reports"



ActiveWorkbook.Close savechanges, True
End Sub

 

[acw]

Hi

How about creating a dynamic defined name and linking the pivot table and graphs to that name (or names as required). That way, when the new data is added, the range will automatically update. You will still have to refresh the pivot tables.

There are plenty of examples of creating these on this site.


HTH

Tony