Saving macros as global addins or personal.xls files

So i have a macro that i wrote that willclean up these datafeeds that i get. i want to be able to use them for each spreadsheet. So the first bit i tried was saving a personal.xls file in the xlstart folder in XP (MSE 2003) . That would automatically pull up but when i tried to run the macro on other worksheets i get the generic 400 error.

the next thing i tried was copying the macro to a module and adding function tags and taking out the sub tags. i then "saved" that in the addins folder then tried assiging my custom button to is through the macros prompt. When i gave the title box the absolute path to the addins folder with clean.xla cited it said it was invalid, so i tried saving it just as clean.xla expecting it to locate the file there anyway.

this far each time i try to run the macro globally it says it cant find the current sheetname!macroname So i must be missing a step somewhere... can anyone give advice, i have searched the forum and couldnt find a similar problem for solutions.

Originally Posted by Leith Ross

Hello Lighthouse13,

The problem may be with the macros themselves, especially if you recorded them. You should post one of your macros for review.

Here is the most recent rendition with function tags instead of sub tags...

Function age(dob As Date)

    ActiveSheet.Name = "Data"
    Sheets("Data").Select
    Columns("B:E").Select
        Selection.Delete Shift:=xlToLeft
        Columns("C:W").Select
        Selection.Delete Shift:=xlToLeft
        Columns("A:A").Select
        Range("A1:B3991").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
           
    Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range
 
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 

Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))
 
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
 
N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If
 
V = Rng.Cells(R, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
End If
Next R
 
EndMacro:
 
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)
 
End Function

Originally Posted by Leith Ross

Hello Lighthouse13,

Other than the Active Sheet name being set to "Data", I don't see anything in the macro that is using specific workbooks or worksheet names.

Did you create your Personal.xls using these steps http://office.microsoft.com/en-us/ex...872961033.aspx?

I did it, and when i went to run the personal.xls!clean macro it said it could not find it. i will run through it again and screen shot the error. maybe that will help?

I am using this on an XP machine with Excel 2003.

So i finally got it though using the iinstructions you posted. There was two things i was missing. Saving the module macro on a seprate file, then exporting it and importing it to personal.xls and binding it to that sheet. Now i have got it to work correct i assigned my custom button to it! Whew that took all day!