if statement which tests for sheet.name in a range of cells

[supper's ready]

i want to create an if statement that says

If sheet.name = range("A:A then

so if the sheet.name is listed within column a then its carried out.

what is the correct syntax?

 

[VoG]

Try like this

If IsNumeric(Application.Match(ActiveSheet.Name, Columns("A"), 0)) Then

 

[supper's ready]

why isnumeric() .. didn't seem to work

 

[VoG]

Are you testing to see if the active sheet's name is found in column A of the same sheet? If so the above code should work.

 

[supper's ready]

no what im trying to do is set the condition
if sheet.name = any of the sheets listed in column a
do this

 

[VoG]

This worked for me

Sub a()
If IsNumeric(Application.Match(ActiveSheet.Name, Columns("A"), 0)) Then MsgBox "Yes"
End Sub

If you want to check if a particular sheet's name is listed on a particular sheet

Sub a()
If IsNumeric(Application.Match(Sheet1.Name, Sheets("Sheet2").Columns("A"), 0)) Then MsgBox "Yes"
End Sub

 

[supper's ready]

hey,
im getting subscript out of range

i think the issue is this export script creates new workbooks and pops them up, making them active so the if statement is looking in the exported sheet which doesn't have the data im looking for. Is there a way to suppress the exported sheets from showing or ever being active?

Sub CreateWorkbooks()

Dim Source As Workbook 'The excel file with all the tabs to be exported
Set Source = ActiveWorkbook 'sets the source to the excel file that is active

Dim Sheet As Object 'the tab/sheet to be exported

Dim strSavePath As String 'the directory where the tab should be exported to

Dim Destination As Workbook 'The excel file that results when export of tab is complete

Dim exportfolder As String

exportfolder = Sheets("Config & directions").Range("C26")

Dim iYearFolder As Integer 'Initial year value (guess)
Dim iMonthFolder As Integer 'Initial month value (guess)
Dim fYearFolder As Variant 'Final year value (set by user)
Dim fMonthFolder As Variant 'Final month value (set by user)

iYearFolder = 0 'initialize to zero
iMonthFolder = 0 'initialize to zero

    'Begin date finding
    'The below code will find the dates to be set as default values in the input boxes. These are guesses to making things easier for the user
    Dim SlashFound As Range
    Dim FirstSlashFound As String
    
    Set SlashFound = Cells.Find("/", , xlFormulas, xlPart) 'look for a /
    
    If Not SlashFound Is Nothing Then 'if a / is found then
        If Not IsDate(SlashFound) Then 'test if its a date, if its not then
            FirstSlashFound = SlashFound.Address 'set FirstSlashFound = to the address of the first / found
            Do
                Set SlashFound = Cells.FindNext(SlashFound) 'go to the next / value
            Loop Until IsDate(SlashFound) Or SlashFound.Address = FirstSlashFound 'keep doing it until the / value is a date or you get back to the original / found (means you looked everywhere)
        End If
    End If
    
    If IsDate(SlashFound) Then 'now that we've looked everywhere, if we got a date we can assign values
        iYearFolder = Year(SlashFound)
        iMonthFolder = Month(SlashFound)
    Else
        MsgBox "No date found on worksheet" 'otherwise we say we couldn't find a date
    End If
    'End date finding
    
Dim ymessage, ytitle As String
Dim mmessage, mtitle As String

ymessage = "Enter the Year for the data (Enter to accept)"
mmessage = "Enter the Month for the data (Enter to accept)"

ytitle = "Year Input Box"
mtitle = "Month Input Box"

fYearFolder = InputBox(ymessage, ytitle, iYearFolder)
fMonthFolder = InputBox(mmessage, mtitle, iMonthFolder)

For Each Sheet In Source.Sheets 'says for every sheet in the Source, do this
If Sheet.Name <> "Config & directions" And Sheet.Name <> "PipelineList" Then
    With Sheet
        If WorksheetFunction.CountA(.Rows(2)) > 0 Then

If Len(Dir(exportfolder & Sheet.Name, vbDirectory)) = 0 Then
MkDir exportfolder & Sheet.Name
MkDir exportfolder & Sheet.Name & "\" & fYearFolder
MkDir exportfolder & Sheet.Name & "\" & fYearFolder & "\" & fMonthFolder
Else
If Len(Dir(exportfolder & Sheet.Name & "\" & fYearFolder, vbDirectory)) = 0 Then
MkDir exportfolder & Sheet.Name & "\" & fYearFolder
MkDir exportfolder & Sheet.Name & "\" & fYearFolder & "\" & fMonthFolder
Else
If Len(Dir(exportfolder & Sheet.Name & "\" & fYearFolder & "\" & fMonthFolder, vbDirectory)) = 0 Then
MkDir exportfolder & Sheet.Name & "\" & fYearFolder & "\" & fMonthFolder
End If
End If
End If

strSavePath = exportfolder & Sheet.Name & "\" & fYearFolder & "\" & fMonthFolder & "\"

Sheet.Copy

Set Destination = ActiveWorkbook


Dim dtMin As Date
Dim dtMax As Date
Dim DayMin As Integer
Dim DayMax As Integer

'How to add a pipeline:
'(1) Add it to the below list

'Begin list of all pipelines in excel sheet
If Sheet.Name = "Central Hudson" _
Or Sheet.Name = "Elizabethtown" _
Or Sheet.Name = "National Grid" _
Or Sheet.Name = "NationalGridLI" _
Or Sheet.Name = "NationalGridNY" _
Or Sheet.Name = "NFGD" _
Or Sheet.Name = "NJNG" _
Or Sheet.Name = "ORU" _
Or Sheet.Name = "Piedmont Natural Gas" _
Or Sheet.Name = "PSEG" _
Or Sheet.Name = "SJG" _
Or Sheet.Name = "CFG" _
Or Sheet.Name = "AGLC" _
Or Sheet.Name = "ConEd" _
Or Sheet.Name = "Gulf South" _
Or Sheet.Name = "Henry Hub" _
Or Sheet.Name = "NFGS" _
Or Sheet.Name = "Niagara" _
Or Sheet.Name = "Transco" _
Or Sheet.Name = "Tennessee" _
Or Sheet.Name = "FCG" _
Then
'End list of all pipelines in excel sheet

'(2) If date is in column A, add it to the below list, if in column B, add it to the column B list.

'Date is in column A
If IsNumeric(Application.Match(Sheet.Name, Sheets("PipelineList").Columns("A"), 0)) Then
Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
dtMin = WorksheetFunction.Min(Columns("A:A"))
dtMax = WorksheetFunction.Max(Columns("A:A"))
DayMin = Day(dtMin)
DayMax = Day(dtMax)
End If

'Date is in column B
If Sheet.Name = "ConEd" _
Or Sheet.Name = "Gulf South" _
Or Sheet.Name = "Henry Hub" _
Or Sheet.Name = "NFGS" _
Or Sheet.Name = "Niagara" _
Or Sheet.Name = "Transco" _
Then
Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy"
dtMin = WorksheetFunction.Min(Columns("B:B"))
dtMax = WorksheetFunction.Max(Columns("B:B"))
DayMin = Day(dtMin)
DayMax = Day(dtMax)
End If

If Sheet.Name = "Tennessee" Then
Columns("E:E").Select
Selection.NumberFormat = "m/d/yyyy"
dtMin = WorksheetFunction.Min(Columns("E:E"))
dtMax = WorksheetFunction.Max(Columns("E:E"))
DayMin = Day(dtMin)
DayMax = Day(dtMax)
End If
Else
DayMin = 0
DayMax = 0
End If

Dim filename As Variant
Dim fmessage, ftitle As String
Dim ifilename As String
ifilename = "Days " & DayMin & "-" & DayMax & " (" & Sheet.Name & ")"
fmessage = "Enter the Filename"
ftitle = "File Name Prompt for " & Sheet.Name
filename = InputBox(fmessage, ftitle, ifilename)

Destination.SaveAs strSavePath & filename
Destination.Close

End If
End With
End If
Next

Exit Sub

End Sub

 

[VoG]

Maybe like this if the sheet with the names is in the same workbook that contains the code

If IsNumeric(Application.Match(Sheet.Name, ThisWorkbook.Sheets("PipelineList").Columns("A"), 0)) Then

 

[supper's ready]

that worked thank you!