VBA - Find a worksheet by name (SOLVED)

[kilowatt]

I have a workbook that gets worksheets created automatically and then names the worksheets based on values it pulls from another application.

Sometimes the database can contain a specific name in this case "Encyclopedia" and if it's gets created I want to search for it and then delete it before the rest of my code gets processed.

I tried to skip the creation of the worksheet all together but the code freaks out a little because it is nested in some other propriatary coding.

Any help would be appreciated.

 

[erik.van.geit]

Hi, kilowatt,

it's a bit confusing to me

do you want to check if a certain sheet already exists and if it exists it should be deleted ?
or did you have something else in mind ?

kind regards,
Erik

 

[kilowatt]

Yes I need to find the sheet within a variable amount of sheets and then delete it if it exists.

 

[erik.van.geit]

something like this might be usefull

Sub test()
    Dim x As Object, sh_name As String
    sh_name = "test"
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sh_name)
      If Err <> 9 Then
      Sheets(sh_name).Delete
      End If
    On Error GoTo 0
    ' do your stuff
End Sub

 

[kilowatt]

That works just as it should. Thank you.

However I have run into another problem as a result of deleting this sheet. If you know how to do it this way it would be better I think.

Here is what I do:

1. Initialize an arrary
2. Populate the arrary with values from a database
3. Cycle through the array and create a worksheet for every name in the arrary. This is where the value "Encyclopedia" comes from.

Here's my problem now:

After I delete the sheet using your code I reference the array in sequential order to do a few other things and it chokes because "Encyclopedia" is not longer a valid worksheet.

If it possible to cycle through the array after it's created and check to see if "Encyclopedia" exists and then delete it out of the array without leaving an empty spot in the array?

Example:

Arrary position 1=Fiction 2=Encyclopedia 3=Reference

Delete the value for position number 2 and then have the arrary read

Arrary position 1=Fiction 2=Reference

 

[erik.van.geit]

After I delete the sheet using your code I reference the array in sequential order to do a few other things and it chokes because "Encyclopedia" is not longer a valid worksheet.

I thought you deleted the sheet to replace it with another version !?

can you clarify a bit more
I'm not sure at all what you're asking now ...

can you post the relevant part of your code ?
(please use the "code" button to display your code)

kind regards,
Erik

 

[kilowatt]

Sorry it's kind of hard to explain because some of the coding is in our own softwares language. But I'll give it a whirl.

This code declares the array and then cycles through the array assigning the values to the corresponding location in the arrary. Then cycles through the new arrary to create the worksheets in the workbook.

Dim Title As String
Dim Temp As String
Dim First As String
Dim Names(10)
Dim i as Integer
Dim strDate as Integer

strDate = Format(Now(), "yyyy")
i=0

Title = "<gateway field name=NAME database=RLC>"
cells(3,4).value = "No. of Items " & strDate-13 & " and older"
cells(3,5).value = "No. of Items " & strDate-12 & " and newer"
cells(6,4).value = "(" & strDate-6 & " and older for encyclopedias)"
cells(6,5).value = "(" & strDate-5 & " and newer for encyclopedias)"

Sheets("Sheet1").Name = Title
First = Title
Temp = Title

<gateway cycle start=1 count=<gateway docs>>
Title = "<gateway field name=NAME database=RLC>"
Names(i) = Title
i=i+1
Cells(1, 1).Value = Title & " Collection Worksheet"
Cells(4, 1).Value = Title
Cells(25, 1).Value = Title
Sheets(Temp).Name = Title
Worksheets(First).Copy before:=Sheets("ExpSheet1")
Temp = First & " (2)"
<gateway endcycle>

Everything that is <gateway ...> is our software's code.

When this finishes running I get the following:

Names(0) = Non Fiction
Names(1) = Fiction
Names(2) = Perodical
Names(3) = Professional
Names(4) = Encyclopedia
Names(5) = Reference
Names(6) = Visual

As a result of the above code I end up with a workbook with 7 worksheets in it all named with one of the above values. Encylopedia can show up anywhere in the list or not at all.

The report I am working on right now happens to have the value Encyclopedia so I was wanting to delete the worksheet. The problem is that if I delete the worksheet when I reference the array later the array still has the value Encylcopedia in it.

So it it possible to cycle through the array before creating all the worksheets and removing the value Encyclopedia without leaving a sequencing gap in the arrary.

I run code after this that selects the first worksheet and then fills in a bunch of data and then goes to the next one and does the same thing.

If you need more info let me know.

 

[erik.van.geit]

or I don't understand what you are trying to do
or you make things more complicated then they should be

Yes I need to find the sheet within a variable amount of sheets and then delete it if it exists.

I thought you were asking to delete a sheet before you inserted another one with the same name
your last reponse is confusing now for me at least

let's take a simple example
one workbook 3 sheets named "a", "b", "c"
what do you want to do now with those sheets ?

I run code after this that selects the first worksheet and then fills in a bunch of data and then goes to the next one and does the same thing.

what's the problem ?
you can easily retrieve the remaining worksheetnames to see if you're working with the right ones ...

if this post is also confusing, I would suggest you start over from scratch as if we didn't write anything here

kind regards,
Erik

 

[tusharm]

Yes, that is the right way to go. Prevention rather than cure.

Option Explicit
Option Base 0
    Sub removeAName(FromArr, RemoveWhat)
        Dim I As Long, J As Long
        On Error Resume Next
        I = Application.WorksheetFunction.Match(RemoveWhat, FromArr, 0) - 1
        If Err.Number <> 0 Then Exit Sub
        On Error GoTo 0
        For J = I To UBound(FromArr) - 1
            FromArr(J) = FromArr(J + 1)
            Next J
        ReDim Preserve FromArr(UBound(FromArr) - 1)
        End Sub
Sub testIt()
    Dim Names()
    ReDim Names(6)
    Names(0) = "Non Fiction"
    Names(1) = "Fiction"
    Names(2) = "Perodical"
    Names(3) = "Professional"
    Names(4) = "Encyclopedia"
    Names(5) = "Reference"
    Names(6) = "Visual"
    removeAName Names, "Perodical"
    Debug.Print Names(2) & ", " & UBound(Names)
    removeAName Names, "Encyclopedia"
    Debug.Print Names(3) & ", " & UBound(Names)
    End Sub

{snip}

So it it possible to cycle through the array before creating all the worksheets and removing the value Encyclopedia without leaving a sequencing gap in the arrary.

{snip}

 

[kilowatt]

Thanks for all the help here guys I've been able to use your code to solve my problem.

Have a much larger problem to deal with now!!

Thanks again,