J
James
I have this code that works fine as long as I am on the "base data" sheet
when I run it but I want it to be a little more flexiable so that you can run
it from any sheet you are on.
I have multiple tabs in a workbook, one is called "base data" and "sheet2".
The macro enters two formulas in column G and H (on the base data tab) and
auto fills them down in that sheet, than in sheet 2 it creates a pivot table
for me with that data from the formulas.
Right now I have the variables like this:
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
And I am sure this is where it screws up on me, how do I make LR just look
at the tab called "base data"
Below is my full code:
Sub OpenIT_format()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("base data")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Sheet2")
Dim PTCache As PivotCache
Dim PRange As Range
'Insert Column headers into base data worksheet
'
Sheets("base data").Select
Range("G1").Select
ActiveCell.FormulaR1C1 = "Hour"
'
Sheets("base data").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "Day"
'Insert Formula for Hour and Day calculations
'
Sheets("base data").Select
Range("G2").Select
ActiveCell.FormulaR1C1 = "=HOUR(RC[-6])"
'
Sheets("base data").Select
Range("H2").Select
ActiveCell.FormulaR1C1 = "=DAY(RC[-7])"
'Range("H3").Select
'Formula was in G2 through H2, wanted to copy down G2 to column H
'
Sheets("base data").Select
'Sheets("base data").Select
Range("G2:H2").AutoFill Destination:=Range("G2:H" & LR)
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SamplePivot")
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields
pt.AddFields RowFields:=Array("User name")
'pt.AddFields ColumnFields:=Array("Hour")
' Set up the data fields
With pt.PivotFields("Elapsed time")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
'This is what I originally came up with, but didn't work
With pt.PivotFields("Hour")
.Orientation = xlColumnField
.Position = 1
End With
'This is what I recorded
' With ActiveSheet.PivotTables("SamplePivot").PivotFields("Hour")
' .Orientation = xlColumnField
' .Position = 1
End With
' Now calc the pivot table
pt.ManualUpdate = False
End Sub
Thanks for the help
when I run it but I want it to be a little more flexiable so that you can run
it from any sheet you are on.
I have multiple tabs in a workbook, one is called "base data" and "sheet2".
The macro enters two formulas in column G and H (on the base data tab) and
auto fills them down in that sheet, than in sheet 2 it creates a pivot table
for me with that data from the formulas.
Right now I have the variables like this:
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
And I am sure this is where it screws up on me, how do I make LR just look
at the tab called "base data"
Below is my full code:
Sub OpenIT_format()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("base data")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Sheet2")
Dim PTCache As PivotCache
Dim PRange As Range
'Insert Column headers into base data worksheet
'
Sheets("base data").Select
Range("G1").Select
ActiveCell.FormulaR1C1 = "Hour"
'
Sheets("base data").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "Day"
'Insert Formula for Hour and Day calculations
'
Sheets("base data").Select
Range("G2").Select
ActiveCell.FormulaR1C1 = "=HOUR(RC[-6])"
'
Sheets("base data").Select
Range("H2").Select
ActiveCell.FormulaR1C1 = "=DAY(RC[-7])"
'Range("H3").Select
'Formula was in G2 through H2, wanted to copy down G2 to column H
'
Sheets("base data").Select
'Sheets("base data").Select
Range("G2:H2").AutoFill Destination:=Range("G2:H" & LR)
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SamplePivot")
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields
pt.AddFields RowFields:=Array("User name")
'pt.AddFields ColumnFields:=Array("Hour")
' Set up the data fields
With pt.PivotFields("Elapsed time")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
'This is what I originally came up with, but didn't work
With pt.PivotFields("Hour")
.Orientation = xlColumnField
.Position = 1
End With
'This is what I recorded
' With ActiveSheet.PivotTables("SamplePivot").PivotFields("Hour")
' .Orientation = xlColumnField
' .Position = 1
End With
' Now calc the pivot table
pt.ManualUpdate = False
End Sub
Thanks for the help