Get a recorded macro to run in different Excel workbooks

[Guest]

I have recorded a Macro to format data and create and format a chart from
that data. I generally have 10 to 15 workbooks with data that need to be
handled with the same macro. I record the macro in the first workbook and
save it to a personnel macro workbook. But for each successive workbook I
have to go in and edit the macro by changing the workbook name. In the
following example this is TOTAL_12 found on the line with **************
following. For example, if I open TOTAL_13 and I manually change TOTAL_12 to
TOTAL_13 , the macro will run just fine in workbook TOTAL_13. When I open
TOTAL_14, I will have to go into the editor and change TOTAL_13 to TOTAL_14,
and so forth.


Sub Data_And_Chart_Formatter()
'
' Data_And_Chart_Formatter Macro
' Macro recorded 6/28/2007 by Brent Ehrlich
'

'
Range("E1").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = "SCFM"
Columns("D").Select
Selection.Replace What:=">", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="<", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.NumberFormat = "h:mm:ss"
Columns("B:C").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("TOTAL_12").Range("B1:C8641"),
_ *****************
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "SCFM"
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 5
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 360
.TickMarkSpacing = 360
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.Orientation = xlUpward
End With
End Sub

Is there a way I can use something like "Option Explicit"? I tried to put
Option Explicit as the very first line in the macro, before Sub
Data_and_Chart_Formatter, but that changed nothing.

 

[Guest]

Your question should be posted to an Excel newsgroup instead of an Access one.

 

[robbinma]

Hi,

Probably the easiest way to solve this is to use an input box e.g.

Dim sheetName As String
sheetName = InputBox("Enter sheet name", "Sheet name prompt", "sheetXX")
MsgBox "sheetname: " & sheetName

and then change the reference as follows:

ActiveChart.SetSourceData Source:=Sheets(sheetName).Range("B1:C8641"),

You need to be careful of someone typing in the wrong sheet name.

You could try Experts exchange for an Excel specific group or one of the
microsoft groups e.g. the groups listed here:
http://www.newsville.com/news/groups/microsoft.public.excel.html

That would be a better place for Excel specific questions

Best regards,

Mark