+ Reply to Thread
Results 1 to 2 of 2

Test to see if a worksheet is present

  1. #1
    Registered User
    Join Date
    10-11-2003
    Location
    Northern CA
    Posts
    5

    Test to see if a worksheet is present

    Using a for next loop I'm having excel create a new worksheet for every part number that is listed in a row on the main sheet. However I want to have it test to see if a worksheet of that name already exists and if it does, I want it to not create another worksheet of that name (which will bug) and resume on to it's next task.
    Here is the foruma, any help would be appreciated:

    For i = 1 To NumberofMDF

    Worksheets("Part_Survey_Form").Activate
    PLTnumber = Cells(8 + i, 1).Value
    MFGnumber = Cells(8 + i, 2).Value

    ' here is where the question is
    ' Test to see if copy of MDF already exists.
    If Worksheets("PLTnumber") = True Then
    Resume



    ' Select sheet "Material Declaration Form" and makes a copy.
    Sheets("Material Declaration Form").Select
    Sheets("Material Declaration Form").Copy After:=Sheets(4)

    ' Change the new MDF sheet name to the P/N is for.
    Sheets("Material Declaration Form (2)").Name = PLTnumber
    Cells(11, 1).Value = PLTnumber
    Cells(11, 2).Value = MFGnumber

    ' clears the cells to be able to enter new information
    Range("C11:D11").Select
    Selection.ClearContents
    Range("D12:N29").Select
    Selection.ClearContents
    Range("A11").Select

    Next i

  2. #2
    Forum Contributor
    Join Date
    11-09-2004
    Posts
    451
    use this function

    it takes sheetname as the parameter

    you can test by using

    If check_sheets(PLTnumber) = True Then


    Function check_sheet(s As String)
    Dim wrk As Worksheet
    Dim k As Integer
    k = 0
    For Each w In Worksheets
    If w.Name = s Then
    k = 1
    End If
    Next
    If k = 1 Then
    check_sheet = True
    Else
    check_sheet = False
    End If
    End Function

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1