plz help with pivot table question

singals

New Member
Joined
Oct 17, 2006
Messages
1
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 :( :cry: :cry: :cry:
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Forum statistics

Threads
1,144,528
Messages
5,724,857
Members
422,586
Latest member
nassardewa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top