[mortimersnerd]

I have a macro that can be run multiple times on a workbook. The first time it is run, it creates multiple worksheet objects.

Code:

Private mySheet As Worksheet

And then I have code like this to create the worksheet and name it:

Code:

Set mySheet = Sheets.Add
    mySheet.Activate
    ActiveSheet.name = "WorkSheet"

I am working on modifying the program so it can be run multiple times, concatenating new data each time it is run. Is there a way to associate the worksheet named "WorkSheet" with the Exceptions worksheet object?

 

[sidcitris]

Code:

mySheet = Worksheets("WorkSheet")

or, to avoid possible errors:

Code:

        Dim i As Integer
        For i = 1 To Worksheets.Count
            If Worksheets.Item(i).Name = "WorkSheet" Then
                mySheet = Worksheets("WorkSheet")                
            End If
        Next

or to run multiple times:

Code:

        Dim wsFound as Boolean
        wsFound = False
        Dim i As Integer
        For i = 1 To Worksheets.Count
            If Worksheets.Item(i).Name = "WorkSheet" Then
                wsFound = True
                mySheet = Worksheets("WorkSheet")                
            End If
        Next

        If Not wsFound Then
             Set mySheet = Sheets.Add
             mySheet.Activate
             ActiveSheet.name = "WorkSheet"
        End If

 

[mortimersnerd]

Thanks! I'll try that in the morning.

 

[mortimersnerd]

When I try the above code, I get the error:

Quote:

Run-time error '91': Object variable or With block variable not set.

 

[Plex]

Quote:

Originally Posted by mortimersnerd;13265551
When I try the above code, I get the error:

Sounds like you are missing a .dll or trying to reference one that is unregistered. Didn't the error come with a line number? If it did, do you mind grabbing us the snippet of code?

 

[mortimersnerd]

The error is on the line where mySheet = Worksheets("....

 

[sidcitris]

The code assumed you still had mySheet declared:

Code:

Private mySheet As Worksheet