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
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