Sheet Name Wildcard

[Jenawade]

How do I use a wildcard in 2007 for the sheet name?

I tried changing this:

With mybook.Worksheets("DF")

to this, and that doesn't work:

With mybook.Worksheets("DF*")

Thank you!

 

[RoryA]

You can't. You can however loop through the sheets and test if the left 2 characters match the text you want?

 

[mikerickson]

You can't use a wildcard in that way

Dim aSheet as Worksheet

For each aSheet in ThisWorkbook.WorkSheets
    if Left(aSheet.Name) = "DF" Then Exit For
Next aSheet

If aSheet Is Nothing Then MsgBox "No DF sheet": Exit Sub

With aSheet
    '....

 

[Jenawade]

You can't. You can however loop through the sheets and test if the left 2 characters match the text you want?

Excellent! How do I do that??? I'm using Ron de Bruin's merge macro:

Sub MergeDemandForecast()
    Dim FirstCell As String
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long

    'Fill in the path\folder where the files are
    'MyPath = "H:\Purchasing\Shared\Demand Forecast\Demand Forecast 2012\2012 DF Analysis\2012 Current DF Files"
    MyPath = "C:\Users\TierneyJA\Desktop\2012 Current DF Files"

    'Add a slash at the end if the user forgets it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop

    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    'Add a new workbook with one sheet
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1

    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
            On Error GoTo 0

            If Not mybook Is Nothing Then

                On Error Resume Next
                
                    Range("A65536").End(xlUp).Select
                    If ActiveCell = "TOTALS" Then
                    Selection.EntireRow.Delete
                    ActiveCell.End(xlUp).Select
                    End If

                 With mybook.Worksheets("DF")
                  FirstCell = "A2"
                  Set sourceRange = .Range(FirstCell & ":" & RDB_Last(3, .Cells))
                  'Test if the row of the last cell >= then the row of the FirstCell
                 If RDB_Last(1, .Cells) < .Range(FirstCell).Row Then
                Set sourceRange = Nothing
             End If
             End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    'if SourceRange uses all columns then skip this file
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceRcount = sourceRange.Rows.Count

                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet"
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        'Copy the file name in column A
                        With sourceRange
                            BaseWks.Cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = MyFiles(Fnum)
                        End With

                        'Set the destrange
                        Set destrange = BaseWks.Range("B" & rnum)

                        'we copy the values from the sourceRange to the destrange
                             sourceRange.Copy
                            With destrange
                              .PasteSpecial xlPasteValues
                              .PasteSpecial xlPasteFormats
                           Application.CutCopyMode = False
                         End With
                        rnum = rnum + SourceRcount
                    End If
                End If
                mybook.Close savechanges:=False
            End If

        Next Fnum
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    Application.Goto BaseWks.Cells(1)

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Calculation = CalcMode
    End With
End Sub

 

[Ruddles]

You can't use a wildcard in that way, you have to look through the Worksheets collection for one that matches. I would probably do something like this:-

Dim myws As Worksheet
Dim bFound As Boolean
 
bFound = False
For Each myws In mybook.Worksheets
  If myws.Name Like "DF*" Then
    bFound = True
    Exit For
  End If
Next myws
If Not bFound Then Set myws = Nothing

This will leave myws pointing to your DF* worksheet if there is one, otherwise myws will not have a value.

Edited: beaten to it!

 

[Jenawade]

Thanks everyone!