If tab name exist, continue to next file - HELP :(

[StevenAncel]

I need my macro to be modified.
Right now it:

• Copies range from 'Order Type vLookup' tab
• Opens files from folder i choose (when prompted)
• Adds new tab to each opened workbook
• Paste the information
• Rename new tab: Order Type vLookup'
• Save and Close Workbook
• Loop for each file in folder

I need it to detect if the tab name already exist

If the tab name exist:

• Close Workbook
• Continue to the next file in the folder
• Loop through all files in folder

If it doesn't exist:

• Paste the information
• Rename new tab: Order Type vLookup'
• Save and Close Workbook
• Loop through all files in folder

Sub Copy_OrderTypevLookup()Dim Fpath As String
Dim Fname As String
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.InitialFileName = "\\ac.local\AC-DFS\UserData\steven.lawson\Desktop\Justins Macro\"
    fd.InitialView = msoFileDialogViewList
    fd.AllowMultiSelect = True


With fd
If .Show = False Then Exit Sub
Fpath = .SelectedItems(1)
End With
Fname = Dir(Fpath & "\*.xlsx")


    Sheets("Order Type vLookup").Select
    Range("A1:C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Order Type vLookup").Select
    Range("A1").Select
    
Do While Fname <> ""
Workbooks.Open Fpath & "\" & Fname
With Sheets(1)
    Sheets("Sheet1").Activate
    Sheets.Add After:=ActiveSheet
     Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    ActiveSheet.Name = "Order Type vLookup"
    Range("A1").Select
End With
    Range("A1").Select
ActiveWorkbook.Close True
Fname = Dir
Loop


End Sub

 

[StevenAncel]

Through research/trial and error. The below macro code does what i need.

I had to make it reference "A1" to see the tab name its looking for.
Couldn't figure out how to make it literally just look at the tab name.

But other than that, works perfect.

Also: I've added a warning message and various file dialog preferences

Sub Copy_JobType()msg = MsgBox("Is the Following True?" & vbCrLf & "-----------------------------------------------" & vbCrLf & "Workbook Name = 'vLookup MacroMaster'" & vbCrLf & vbCrLf & "A1 = Tab Name", vbYesNo)
If msg = vbNo Then
    Exit Sub
End If
On Error Resume Next
Dim Fpath As String
Dim Fname As String
Dim fd As FileDialog
Dim sh As Worksheet, shNam As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.InitialView = msoFileDialogViewList
    fd.AllowMultiSelect = True
    fd.Title = "Choose Folder Containing Files"
    fd.ButtonName = "Choose This Folder"
    fd.InitialFileName = "Choose Folder :)"




With fd
If .Show = False Then Exit Sub
Fpath = .SelectedItems(1)
End With
Fname = Dir(Fpath & "\*.xlsx")


    Sheets("Job Type vLookup").Select
    Range("A2:B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Job Type vLookup").Select
    Range("A1").Select
    
Do While Fname <> ""
Workbooks.Open Fpath & "\" & Fname
shNam = Workbooks("vLookup MacroMaster.xlsm").Sheets(1).Range("A1").Value


If Not SheetExists(ActiveWorkbook.Name, shNam) Then
With Sheets(1)
    Sheets("Sheet1").Activate
    Sheets.Add after:=ActiveSheet
     Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    ActiveSheet.Name = "Job Type vLookup"
    Range("A1").Select
End With
Range("A1").Select
ActiveWorkbook.Close True
Fname = Dir
Else
Range("A1").Select
ActiveWorkbook.Close True
Fname = Dir
End If
Loop
End Sub

 

[StevenAncel]

I forgot to add the function it uses to work as well:

Function SheetExists(wbName As String, shName As String) As BooleanSheetExists = False
With Workbooks(wbName)
    For Each sh In .Sheets
        If sh.Name = shName Then
            SheetExists = True
            Exit For
        End If
     Next sh
End With
End Function