Wildcard for Different Sheet names

**eagle670** · 12-09-2014, 10:40 AM

I have a macro that I wrote for a specific spreadsheet and I now want to use that macro for other sheets that contain the same data, but have a different worksheet name. I want the macro to ignore the first to characters of the sheet name and I am unsure how to do this.

Here is the specific part of the macro where the sheet name comes into play. I would like the macro to ignore the sheet name all together or just the first 2 characters, because that is the only difference between the speadsheets that I will run it against.

Cells.Select
ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort.SortFields. _
Add Key:=Range("A2:A34239"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort.SortFields. _
Add Key:=Range("H2:H34239"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort
.SetRange Range("A1:S34239")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

**JOHN H. DAVIS** · 12-09-2014, 11:31 AM

Please use code tags when posting. Refer too Forum Rule No. 3. Maybe:

Sub eagle670()
Dim i As Long
'To loop through all sheets that don't begin with "FW"
For i = 1 To Sheets.Count
    If Left(Sheets(i).Name, 2) <> "FW" Then
        Sheets(i).Range("A1").Value = "Great"
    End If
Next i

'OR
If Left(ActiveSheet.Name, 2) <> "FW" Then
    GoTo zz:
End If
zz:
'REST OF THE CODE
End Sub

**Norie** · 12-09-2014, 11:33 AM

Change Worksheets("FW POG CONVERSION REFRESH FILE") to ActiveSheet and the code will run on whicheever sheet is currently active.

**alansidman** · 12-09-2014, 11:35 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

**eagle670** · 12-09-2014, 11:45 AM

Here is the code

Sub DISC_DUPLICATES_W_SUBCODE()
'
' DISC_DUPLICATES_W_SUBCODE Macro
'

'
   Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    Range("K25748").Select
    ActiveCell.FormulaR1C1 = "X"
    Selection.End(xlUp).Select
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],C[-9],1,FALSE)"
    Range("K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$T$35000").AutoFilter Field:=8, Criteria1:= _
        "=DISCONTINUED", Operator:=xlAnd
    ActiveSheet.Range("$A$1:$T$35000").AutoFilter Field:=11, Criteria1:=">=1", _
        Operator:=xlAnd, Criteria2:="<=999999"
    Rows("72:72").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    Range("A1").Select
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Cells.EntireColumn.AutoFit
    Cells.Select
   Cells.Select
    Cells.EntireColumn.AutoFit
    Cells.Select
    ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort.SortFields. _
        Add Key:=Range("A2:A34239"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort.SortFields. _
        Add Key:=Range("H2:H34239"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort
        .SetRange Range("A1:S34239")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]=R[-1]C[-1]"
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.End(xlDown).Select
    Range("B28797").Select
    ActiveCell.FormulaR1C1 = "X"
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "FALSE"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "MATCH 1"
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-1]C[-8]=""FALSE"",""FALSE"",IF(RC[-1]=""DISCONTINUED"",""TRUE""))"
    Range("I2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToLeft).Select
    Range("K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    Range("J28797").Select
    ActiveCell.FormulaR1C1 = "X"
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MATCH 2"
    Range("J2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$U$34239").AutoFilter Field:=10, Criteria1:="=TRUE" _
        , Operator:=xlAnd
    Rows("69:69").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Selection.AutoFilter
    Cells.Select
    ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort.SortFields. _
        Add Key:=Range("A2:A34239"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("FW POG CONVERSION REFRESH FILE").Sort
        .SetRange Range("A1:U34239")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
End Sub

Wildcard for Different Sheet names

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Wildcard for Different Sheet names

Re: Wildcard for Different Sheet names

Re: Wildcard for Different Sheet names

Re: Wildcard for Different Sheet names

Re: Wildcard for Different Sheet names

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Wildcard for Different Sheet names

[SOLVED] Create an Array which has Sheet Names excluding a Few Sheet Names to be used in a MACRO

Switch between open other Workbooks with wildcard (other wb workbook can have diff names)

Create a Formula That Uses a Wildcard for Multiple Workbook Names

Getting a list of Sheet Names by excluding certain Sheet Names instantenously..

Bookmarks

Bookmarks

Posting Permissions