• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro Spanning Over More than One Tab

D

Door

New Member
Hello,

I recently tried to record and use a macro to simplify a long and tedious process: I go to a component/part tab, type in Cum. Cost, create the column from existing data, then I take the data and create a pivot table and pivot chart on a new sheet. However, I've run into a few problems.

My macro does not work when I run it on another component tab. It seems to halt/fail when creating the new sheet for the pivot table. Furthermore, the length of the columns that are filled with data may vary from part to part, which creates a problem since the recorded macro only pulls up that many rows of data and not the amount individual to each tab.

I have no knowledge of VBA, but I'd really like to make my analytical process more efficient by not wasting time creating pivot tables and charts for each individual sheet; a macro would be more practical and a huge time-saver.

Sorry for the long code (I only included it if you need to reference it, the bolded is the first part where Excel finds an issue when the macro is run.)

I appreciate your help,

-Door

Sub LeadTime()
'
' LeadTime Macro
'
'
ActiveCell.FormulaR1C1 = "Cumulative Cost"
Range("L2").Select
Columns("L:L").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = ""
Range("G2").Select
ActiveWorkbook.Worksheets("GA58ATDK_REV_008").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GA58ATDK_REV_008").Sort.SortFields.Add Key:=Range( _
"G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("GA58ATDK_REV_008").Sort
.SetRange Range("A2:L236")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("L2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]"
Range("L3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-2]"
Range("L3").Select
Selection.AutoFill Destination:=Range("L3:L236"), Type:=xlFillDefault
Range("L3:L236").Select
Range("G1:L236").Select
Range("L236").Activate
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"GA58ATDK_REV_008!R1C7:R236C12", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Leadtime")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Cumulative Cost"), "Sum of Cumulative Cost", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Cumulative Cost"), "Sum of Cumulative Cost2", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Sum of Cumulative Cost")
.Calculation = xlRunningTotal
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Sum of Cumulative Cost2")
.Calculation = xlPercentRunningTotal
.NumberFormat = "0.00%"
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ApplyChartTemplate ( _
"C:\Users\andkor\AppData\Roaming\Microsoft\Templates\Charts\LeadTimeProfile.crtx" _
)
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$3:$C$11")
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.475, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.5295137066, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.0282485876, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.041997854, msoFalse, _
msoScaleFromTopLeft
Range("C24").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -46.5
ActiveSheet.Shapes("Chart 1").IncrementTop 128.25
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.0494505495, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0043570697, msoFalse, _
msoScaleFromBottomRight
Range("L8").Select
ActiveWindow.SmallScroll Down:=6
Sheets("GA58ATDK_REV_008").Select
Sheets("GA58ATDK_REV_008").Name = "GA58ATDK_REV_008"
Sheets("Sheet1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
ActiveChart.Paste
ActiveChart.ChartTitle.Text = "GA58ATDK Lead Time Profile"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"GA58ATDK Lead Time Profile"
With Selection.Format.TextFrame2.TextRange.Characters(1, 26).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 26).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "% BOM at Lead Time"
Selection.Format.TextFrame2.TextRange.Characters.Text = "% BOM at Lead Time"
With Selection.Format.TextFrame2.TextRange.Characters(1, 18).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 18).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Lead Time (weeks)"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Lead Time (weeks)"
With Selection.Format.TextFrame2.TextRange.Characters(1, 17).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 17).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Text = _
"Cum BOM $$ at Lead Times Indicated"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Cum BOM $$ at Lead Times Indicated"
With Selection.Format.TextFrame2.TextRange.Characters(1, 34).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 34).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.ChartTitle.Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "GA58ATDK"
Range("C22").Select
End Sub
 
In the bolded part, you'll see that it assigns a name to the PivotTable. Since you (presumably) already have a "PivotTable1" you need to setup a way to create a unique name. You can get a count of PivotTable with something like:
x = ThisWorkbook.PivotCaches.Count + 1
and concatenate that with your name.
 
So where in the code would I need to input that count line? Before the bolded part or inside of it where it's asking me to assign the name? I have some Matlab coding experience and basic HTML, but I'm not very familiar with VBA and some of the formatting seems a tad counter-intuitive.
 
It would be before. You'll be wanting to setup a variable for the PivotTable name. So, perhaps something like:
Code: 
Dim x as Integer
Dim ptName as String
 
x = ThisWorkbook.PivotCaches.Count
ptName = "PivotTable" & x
'other setup code goes here...
'Then, everywhere you later reference that PivotTable 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"GA58ATDK_REV_008!R1C7:R236C12", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:=ptName _
, DefaultVersion:=xlPivotTableVersion14
 
'Note the use of ptName in the call
'...more coding
 
You must log in or register to reply here.
Back
Top