Work sheet find

[Nuwan]

Hi,
I want to find and compare a work sheet with my given name.

Dim NewSheetName as String
For Each Ws.Name In ThisWorkbook.Worksheets
Set Find1 = .Find(What:=NewSheetName, MatchCase:=True)
If Not Find1 Is Nothing Then
'-----------------------------------True condition

Else
'-----------------------------------False condition
Endif



above macro not working.............

help me

Nuwan

 

[Leith Ross]

Hello Nuwan,

To compare the worksheets' names to a specific name...

Sub Macro1()
  Dim NewSheetName As string
  Dim Wks As Worksheet

    For Each Wks In Worksheets
      If Wks.Name = NewSheetName Then
      
      Else
      
      End If
    Next Wks
End Sub

Sincerely,
Leith Ross

 

[Nuwan]

Hi Leith,
This is different.

I want to search all sheets at once and if not exists do my else job
this is looping until "worksheet name list" end in my another sheet

Dim TT as integer
Dim NewSheetName as String


TT=1
do
NewSheetName=sheet1.range("A" & TT).text

For Each Ws.Name In ThisWorkbook.Worksheets
Set Find1 = .Find(What:=NewSheetName, MatchCase:=True)
If Not Find1 Is Nothing Then
'-----------------------------------True condition

Else
'-----------------------------------False condition
Endif
Next Ws
TT=TT+1
loop until sheet1.range("A" & TT).text=""

above macro not working.............

Dim NewSheetName as String
With ThisWorkbook.Worksheets(Ws.Name)
Set Find1 = .Find(What:=NewSheetName, MatchCase:=True)
If Not Find1 Is Nothing Then
'-----------------------------------True condition

Else
'-----------------------------------False condition
Endif
End with

2nd method also not correct

pls help me

Nuwan

 

[Nuwan]

Pls see next

 

[Nuwan]

Hi,

"Find" is compulsory in my macro. because

If Not Find1 Is Nothing Then
'-----------------------------------True condition

Else

Sheets.Add.Name = sheet1.range("A" & TT).text

Endif

Nuwan

 

[Leith Ross]

Hello Nuwan,

OK, you want to search a range for a given worksheet name.

Sub Macro1()
  Dim NewSheetName As String
  Dim TT As Long
  Dim Ws As Worksheet

    TT = 1

      For Each Ws In ThisWorkbook.Worksheets
        NewSheetName = Sheet1.Cells(TT, "A")
        If NewSheetName = "" Then Exit Sub
        Set Find1 = Ws.Cells.Find(What:=NewSheetName, _
                                  After:=Ws.Cells(1, 1), _
                                  LookIn:=xlValues, _
                                  LookAt:=xlWhole, _
                                  SearchOrder:=xlByRows, _
                                  SearchDirection:=xlNext, _
                                  MatchCase:=False)
        If Not Find1 Is Nothing Then
          'Code for match found
        Else
          'Code for no match found
        End If
        TT = TT + 1
      Next Ws
End Sub

Sincerely,
Leith Ross

 

[Nuwan]

Dear leith,

still there is a problem, I want to create new sheet if sheet not exists
(for next loop try to add same sheet each time, of the loop)

see next post
nuwan

 

[Nuwan]

Dear leith,

still there is a problem, I want to create new sheet if sheet not exists
("for next" loop try to add same sheet each and every time and generate errors)

Sub Macro1()
Dim NewSheetName As String
Dim TT As Long
Dim Ws As Worksheet

Do
'----------------------'Code for match found 1
Else
NewSheetName = sheet1.range("A" & TT).text
For Each Ws In ThisWorkbook.Worksheets

If NewSheetName = "" Then Exit Sub
Set Find1 = Ws.Cells.Find(What:=NewSheetName, After:=Ws.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Find1 Is Nothing Then
'---------------------'Code for match found 2
Else
'---------------------Sheets.Add.Name = sheet1.range("A" & TT).text
End If
End With
Next Ws
End If
TT = TT + 1
Loop Until sheet1.range("A" & TT).text ""
End Sub

How to solve this

nuwan