VBA to fix a Dim
 

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:=xlDatab ase,
SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutpu t.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

VBA to fix a Dim
 

I assume this code is in a standard code module. Assuming that to be the case
then the default sheet is the active sheet so your line of code

LR = Range("A" & Rows.Count).End(xlUp).Row
is the same as
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

To make it sheet specific change it to
LR = Sheets("Base Data").Range("A" & Rows.Count).End(xlUp).Row

--
HTH...

Jim Thomlinson


"James" wrote:

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:=xlDatab ase,
SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutpu t.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

VBA to fix a Dim
 

Jim,
That worked perfect. Thank you for that helpful insight.

"Jim Thomlinson" wrote:

I assume this code is in a standard code module. Assuming that to be the case
then the default sheet is the active sheet so your line of code

LR = Range("A" & Rows.Count).End(xlUp).Row
is the same as
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

To make it sheet specific change it to
LR = Sheets("Base Data").Range("A" & Rows.Count).End(xlUp).Row

--
HTH...

Jim Thomlinson


"James" wrote:

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:=xlDatab ase,
SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutpu t.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