PDA

View Full Version : Create worksheets from a list



jjj2k
07-11-2011, 08:40 PM
Hi all I have a vba script that creates tabs from a worksheet called "Query". It starts from A2 (worksheet name) and B2 (the sql connection string to use).

The script runs fine if I have the same number of tabs and their names are unchanged. But crashes when I add a new row to the Query but I can get around it by deleting all the sheets in the list but I don't want to do it.

Any fixes?



Sub CreateTab()

Dim sh As Worksheet, flg As Boolean
Dim i As Integer
Dim r As Range
Dim shName As String


Sheets("Query").Select
ActiveSheet.Range("a1").Select


'Count the end row
i = Selection.End(xlDown).Row

'Loop from A2 to A(i) to Create Sheets
For Each r In Range("a2:a" & i)
For Each sh In Worksheets
If sh.Name = r.Value Then flg = True: Exit For
Next
If flg = True Then
Sheets("Query").Select
ActiveSheet.Range("a1").Select
Else
Sheets.Add.Name = r.Value
End If
ActiveSheet.Move after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Next

Sheets("Query").Select
ActiveSheet.Range("a2").Select

End Sub

CatDaddy
07-12-2011, 12:27 PM
your flg logic is a little confusing, but i have a few suggestions:

For each sh in ActiveWorkbook.Worksheets

If sh.Name = r.Value Then Exit For
Else
Sheets.Add.Name = r.Value
End If