Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|