LOOPING and create sheets automaticlly

[sal21]

Code: Select all

Do While Not RS.EOF
TEST = Trim(RS(0).Value)
debug.print TEST
CONTA = CONTA + 1
RS.MoveNext
Loop

I use thi piece of code to loop into recordest

How to create and add a new sheet, if just it not exists, in workbook base the var TEST

[HansV]

Try:

Code: Select all

Dim wsh As Worksheet
Do While Not RS.EOF
  TEST = Trim(RS(0).Value)
  On Error Resume Next
  ' Try to refer to worksheet
  Set wsh = Worksheets(TEST)
  ' If an error occurred, the sheet doesn't exist
  If Err Then
    ' Create new sheet
    Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    ' Name the new sheet
    wsh.Name = TEST
  End If
  On Error GoTo 0
  CONTA = CONTA + 1
  RS.MoveNext
Loop

[sal21]

Try:

Code: Select all

Dim wsh As Worksheet
Do While Not RS.EOF
  TEST = Trim(RS(0).Value)
  On Error Resume Next
  ' Try to refer to worksheet
  Set wsh = Worksheets(TEST)
  ' If an error occurred, the sheet doesn't exist
  If Err Then
    ' Create new sheet
    Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    ' Name the new sheet
    wsh.Name = TEST
  End If
  On Error GoTo 0
  CONTA = CONTA + 1
  RS.MoveNext
Loop

Hans no words!!!!
tks.

hummmmmm....
But instead to add the generic sheet if not is present, is possible to use a sheet SERVIZIO and rename it with TEST var

Note:
The sheet SERVIZIO is in the active workbbook.

[HansV]

Change the line

Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))

to

Worksheets("SERVIZIO").Copy After:=Worksheets(Worksheets.Count)
Set wsh = Worksheets(Worksheets.Count)

[sal21]

Change the line

Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))

to

Worksheets("SERVIZIO").Copy After:=Worksheets(Worksheets.Count)
Set wsh = Worksheets(Worksheets.Count)

Hans....
Sure you just have answared me about:
"Delete all sheet in workbook but not the SHEET1 and SHEET2..."
but not remember how to...

[HansV]

You can create a loop:

Code: Select all

Dim i As Integer
Application.DisplayAlerts = False
For i = Worksheets.Count To 1 Step -1
  Select Case UCase(Worksheets(i).Name)
    Case "SHEET1", "SHEET2"
      ' Do nothing
    Case Else
      Worksheets(i).Delete
  End Select
Next i
Application.DisplayAlerts = True

[sal21]

You can create a loop:

Code: Select all

Dim i As Integer
Application.DisplayAlerts = False
For i = Worksheets.Count To 1 Step -1
  Select Case UCase(Worksheets(i).Name)
    Case "SHEET1", "SHEET2"
      ' Do nothing
    Case Else
      Worksheets(i).Delete
  End Select
Next i
Application.DisplayAlerts = True