Excel 2007- Raw Data & Macros - Sheet name changes cause issues 1

[ladyck3]

I've been using the personal.xlsb hidden file for awhile now, thanks to this forum (GO MACROS) but I'm not a macro or code person. I can record macros and have not had problems until today.

I have 2 macros that I currently use that are stored in that file, and when I use them it can be on any worksheet no matter the name of the worksheet as long as the data is, of course, the same structure.

Today I recorded a macro and in my test file, the worksheet that the data resided was called "Sheet1" and it worked SWIMMINGLY....
So after practicing the macro for a few attempts, I went on to run it on the real data.

The real data resides in a different workbook and the worksheet name is TRAN20110601 and the raw data I get tomorrow will be labeled TRAN20110602 see the difference?

In the two macros that I can run on anything, there is no mention of a worksheet name so it just runs but on this data, every time I try to record the macro, the end result shows a sheet name so the macro is not compatible universally... its driving me crazy.

Any tips?
My macro sorts the first column by transaction #, then removes unnecessary columns, renames the ones left standing to new names, adds column filters and freezes the first row/pane.

Here is my macro, how do I eliminate the need to go in and edit the macro every time before I use it to change the worksheet name?

Thanks in advance...

Sub TARP()
'
' TARP Macro
' Clean Raw GCIC Data for TARP Report
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Columns("A:A").EntireColumn.AutoFit
    Range("A1").Select
    ActiveWorkbook.Worksheets("TRAN20110602").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TRAN20110602").Sort.SortFields.Add Key:=Range("A1" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("TRAN20110602").Sort
        .SetRange Range("A2:CF5262")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:AX").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Transaction No."
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Product"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Posting Date"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Symptom 3 Level Text"
    Columns("D:D").Select
    Selection.Cut
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Appointment Telephone"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Creator Name"
    Columns("A:G").Select
    Columns("A:G").EntireColumn.AutoFit
    Selection.AutoFilter
    Range("A2").Select
    ActiveWindow.FreezePanes = True
End Sub

I appreciate the help, as always...
Laurie


LadyCK3
aka: Laurie

 

[SkipVought]

hi,

in the VB Editor

put your cursor in the TARP macro

Menu -- Edit > Replace

Select Search CURRENT PROCEDURE

in the Find What: paste ActiveWorkbook.Worksheets("TRAN20110602")

in the Replace With: paste activesheet

Now whenever you open the workbook and activate appropriate sheet, the TARP macro will run regardless the sheet name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

I know its been a LONG TIME.... but I gotta say..... in a most plutonic way....

LUV YA MAN!!!

Oh this just makes my life so much simpler... when I retire, I'll learn to do this coding stuff... that's about when I just MIGHT have time.

I sure appreciate your assistance in between my hiccups...

Skip, you are once again my HERO

!!!!!

Laurie


LadyCK3
aka: Laurie