+ Reply to Thread
Results 1 to 2 of 2

Macro Problem - Having Original Macro Worksheet Open

  1. #1
    Registered User
    Join Date
    11-25-2008
    Location
    Ann Arbor
    Posts
    5

    Macro Problem - Having Original Macro Worksheet Open

    I am new to the world of macros.

    I want to use a macro to perform a series of calculations. I created a macro to work for this but it seems that I have to have the original file that contains the macro open in order for it to work on a new file. Is there a way I can perform this macro on a new worksheet without having the original open?

    Here is my macro code:

    Sub Fuel_Calculations()
    '
    ' Fuel_Calculations Macro
    ' Fuel Calculations for Percentage, Total Fuel, and Fuel Breakdown
    '

    '
    ActiveCell.FormulaR1C1 = "Percentage"
    With ActiveCell.Characters(Start:=1, Length:=10).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
    "=RC[-8]/SUMIF(RC[-11]:R[4998]C[-8],RC[-11],RC[-8]:R[4998]C[-8])"
    Columns("L:L").Select
    Selection.NumberFormat = "0.00%"
    Columns("L:L").EntireColumn.AutoFit
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Total Fuel"
    With ActiveCell.Characters(Start:=1, Length:=10).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
    "=SUMIF(Fuel!RC[-12]:R[4998]C[-10],'GL Paybook'!RC[-2],Fuel!RC[-10]:R[4998]C[-10])"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
    "=SUMIF(Fuel!R2C1:R5000C3,'GL Paybook'!RC[-2],Fuel!R2C3:R5000C3)"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=RC[-8]/SUMIF(R2C1:R5000C4,RC[-11],R2C4:R5000C4)"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Fuel Breakdown"
    With ActiveCell.Characters(Start:=1, Length:=14).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
    Columns("M:N").Select
    Selection.NumberFormat = "$#,##0.00"
    Columns("M:N").EntireColumn.AutoFit
    Range("L2").Select
    End Sub

  2. #2
    Registered User
    Join Date
    11-06-2008
    Location
    Canada
    Posts
    36
    If you are just doing this for yourself only then you can save it to you PERSONAL.XLS file in the following location

    C:\Documents and Settings\ . . . \Application Data\Microsoft\Excel\XLSTART

    which will make it available for all spreadsheets you open. Or you could save the macro as an Add-In. Just "Save As" and then select "Excel Add-In" for the "Save As Type" option. You can then add it as an Add-In to Excel.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1