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
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