VBA- Find tab by cell reference, if no tab by that name add tab and name it

[Anthony2687]

Hello All,

With the help of this forum I have create the below Code, but it just hit me that I may run into instances where this code That copies data and pastes it to a tab by a cell reference may infact stop if the cell reference encounters a time where a tab doesnt share its name.

So If you could, I need you assistance again. How would you recommend I add the second code into the larger first code? I'm thinking maybe an if statment where I've bolded the code?

Sub Loop1()</SPAN>

Do</SPAN>

Sheets("Sheet2").Range("$A$1:$K$199").AutoFilter Field:=4, Criteria1:=Range("A201").Value, Operator:=xlAnd</SPAN>

Range("A2:K200").Select</SPAN>

Application.CutCopyMode = False</SPAN>

Selection.Copy</SPAN>

Sheets(Sheet2.Range("A201").Text).Select</SPAN>

Cells(Rows.Count, "A").End(xlUp)(2).Select</SPAN>

ActiveSheet.Paste</SPAN>

Sheets("Sheet2").Select</SPAN>

Rows("201:201").Select</SPAN>

Application.CutCopyMode = False</SPAN>

Selection.Delete</SPAN>

Loop Until IsEmpty(ActiveCell)</SPAN>

End Sub Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Paste ActiveSheet.Name = Range("D2").Value</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Warship]

try this just before "Do"

    SheetExists = False
    On Error Resume Next
    SheetExists = Sheets(Sheets("Sheet2").Range("A201").Text).Name <> ""
    On Error GoTo 0
    
    If Not SheetExists Then
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheets(Sheets("Sheet2").Range("A201").Text)
    End If
    
    Sheets("Sheet2").Select

 

[Anthony2687]

Thanks Warship! What you provided didn't exactly work, it got jammed up, but I took the code you wrote and put it within mine. My problem now is it paste the copied data on "Sheet2" as well as the tab with the corresponding name. I can't figure out where in the code it has it paste on "Sheet2"

Do
Sheets("Sheet2").Range("$A$1:$K$199").AutoFilter Field:=4, Criteria1:=Range("A201").Value, Operator:=xlAnd
Range("A2:K200").Select
Application.CutCopyMode = False
Selection.Copy
SheetExists = False
On Error Resume Next
SheetExists = Sheets(Sheets("Sheet2").Range("A201").Text).Name <> ""
Cells(Rows.Count, "A").End(xlUp)(2).Select
ActiveSheet.Paste
On Error GoTo 0
If Not SheetExists Then
Sheets.Add After:=Sheets(Sheets.Count)
Cells(Rows.Count, "A").End(xlUp)(2).Select
ActiveSheet.Paste
ActiveSheet.Name = Range("D2").Value
End If

Sheets("Sheet2").Select
Rows("201:201").Select
Application.CutCopyMode = False
Selection.Delete
Loop Until IsEmpty(ActiveCell)

 

[Anthony2687]

So I've tweeked the code a bit. My new issue is that when it creates a new tab it will paste the correct information on the new tab and name it correctly, but it will also paste that information on the master file. I think my issue is within the SheetEcists, but I'm not sure. Does anyone have advice?

Do
Sheets("Sheet2").Range("$A$1:$K$299").AutoFilter Field:=4, Criteria1:=Range("A301").Value, Operator:=xlAnd
Range("A2:K300").Select
Application.CutCopyMode = False
Selection.Copy
SheetExists = False
On Error Resume Next
SheetExists = Sheets(Sheets("Sheet2").Range("A301").Text).Select <> ""
Cells(Rows.Count, "A").End(xlUp)(2).Select
ActiveSheet.Paste
On Error GoTo 0
If Not SheetExists Then
Sheets.Add After:=Sheets(Sheets.Count)
Cells(Rows.Count, "A").End(xlUp)(2).Select
ActiveSheet.Paste
ActiveSheet.Name = Range("D2").Value
End If

Sheets("Sheet2").Select
Rows("301:301").Select
Application.CutCopyMode = False
Selection.Delete
Loop Until IsEmpty(ActiveCell)

 

[Warship]

You have (2) ActiveSheet.Paste
The first one is pasting to the master, which is the active sheet at that time.
The second pastes to the newly added sheet, which is the active sheet as soon as you add it.