Overclock.net banner

1 - 7 of 7 Posts

·
Premium Member
Joined
·
11,540 Posts
Discussion Starter #1
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:

Code:
Private mySheet As Worksheet
And then I have code like this to create the worksheet and name it:

Code:

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?
 

·
Registered
Joined
·
99 Posts
Code:
Code:
mySheet = Worksheets("WorkSheet")
or, to avoid possible errors:

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

·
Premium Member
Joined
·
11,540 Posts
Discussion Starter #4
When I try the above code, I get the error:

Quote:


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

 

·
Registered
Joined
·
3,233 Posts
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?
 
1 - 7 of 7 Posts
Top