Referring to a worksheet list within a macro

[ganders]

Hi all,

I was hoping someone could help me with a query. I've got a working macro that loops through all the worksheets in a workbook, but excludes certain sheets that I specify in the code. This is done with the following:

IF worksheet.name <> "Pricing Source" AND worksheet.name <> "Control" THEN...

I'd like to know how to do two things. Firstly, how do you write out a list of names rather than having loads of 'AND's, i.e. something like:

If worksheet.name <> ("Pricing Source", "Control", "Name3", "Name4") THEN...

Secondly, and what will probably be more tricky, I was hoping to find out how to reference a list in a worksheet within the workbook. So, for example, say I have a list in column A of a sheet called 'Pricing Source'. This list corresponds to the names of all the worksheets in the workbook that I would like the macro to loop though. Is there a simple way to reference the list within th macro, so that it will work something like:

IF worksheetname IN (list of names in column A of 'Pricing Source') THEN...

To complicate things just a tiny bit, I don't want the first row of column A to be included in the list, and the list is likely to grow longer or shorter over time, so it can't be a static reference of certain cells.

Any help would be much appreciated!

[Kenneth Hobson]

Sub InterateSheetNames()
  Dim cell As Range
  For Each cell In Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp))
    MsgBox cell.Value2
  Next cell
End Sub

[tlafferty]

The first thing I'd suggest is trying the Select Case structure for branching and control. This will allow you to flexibly make decisions based on the name of each sheet:

Sub WorksheetChecker()
    For Each s In Sheets
        Select Case s.Name
            Case Is <> "Pricing"
            Case Is <> "Control"
        End Select
    Next s
End Sub

[ganders]

Hi,

Thanks very much to both of you for your replies. I have to admit that I'm rather inexperienced with macros. I'm working with a piece of code that I found elsewhere, which I've changed ever so slightly and which now works perfectly for me (apart from what I've mentioned above). Unfortunately, I'm a bit unsure how to actually incorporate your suggestions into the code. I've given it a go, but it isn't working for me.

Perhaps it would help if I showed you the full code. The part I wanted to edit starts " If sh.Name <> DestSh.Name..."

Sub CopyDataWithoutHeaders()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Pricing").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Pricing"

    StartRow = 2

    For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> DestSh.Name And sh.Name <> "AccessLink" And sh.Name <> "Control" Then
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            If shLast > 0 And shLast >= StartRow Then
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                   MsgBox "There are not enough rows in the " & _
                   "summary worksheet to place the data."
                   GoTo ExitTheSub
                End If

                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
                
                DestSh.Cells(Last + 1, "C").Resize(CopyRng.Rows.Count).Value = sh.Name


            End If

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub