PDA

View Full Version : Solved: Determine is Sheet exists based on list



dvenn
04-16-2007, 08:30 AM
Sorry if this exists somewhere.. I did try searchig for it.


here is the problem

On sheet1 I have a list in Column T with some names in it.

What I would like to do is loop thru this list and determine is a sheet with that name exists in the workbook, if it does not, copy a template sheet to a sheet with that name, then perform some additonal macros on the sheet... if it does exist , select/activate that sheet, then run some macros..

Thanks in advance

Danny

Bob Phillips
04-16-2007, 08:43 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If Not SheetExists(.Cells(i, "A").Value) Then
Worksheets.Add.Name = .Cells(i, "A").Value
End If
myMacro Worksheets(.Cells(i, "A").Value)
Next i

End With

End Sub


'-----------------------------------------------------------------
Function SheetExists(sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(sh) Is Nothing)
On Error GoTo 0
End Function

dvenn
04-16-2007, 09:20 AM
Thank you xld for the quick response.

Not exactly what I was looking for but was morethan enough to customize to my needs.

As always thanks to all those here at VBAExpress

Bob Phillips
04-16-2007, 09:31 AM
PLease tell me how it wasn't, as it looks to do EXACTLY what you asked, so I would be interested in knowing how I mis-interpreted it.

dvenn
04-16-2007, 11:03 AM
No mis-interpretation on your part (at least that I can tell).

I needed it to copy a template if it didn't exist.

I also removed the CONST since the CONST was only used in this routine once.

here is the slight modification I made (probably dirty code, but it did work)
If Not SheetExists(.Cells(i, "T").Value) Then
Worksheets("Template").Visible = True
Worksheets("Template").Activate
ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
Worksheets("Template (2)").Name = .Cells(i, "T").Value
Worksheets("Template").Visible = False
End If