Object vairable - 91 error on setting a worksheet variable

cova

Board Regular
Joined
Jun 9, 2010
Messages
84
OK, so I have this code here, which will let me know whether the info entered is new data or changes to old data and put it into the correct sheet depending on which it is.
Problem is, when I get to the if statement, when it gets to ws2= it causes the run-time error 91: Object variable or with-block variable not set
Thing is, the variable is set on line 3. Even recognises it if I change it to Ws2 and converts it to correct formatting.

Code:
Sub copysheetdata()
Dim custID As String
Dim ws1, ws2 As Worksheet
Dim i, j As Integer
Set ws1 = Sheets(ActiveSheet.Name)
custID = ws1.Range("D3").Value
Sheets("slcustm").Select
Columns("B:B").Select
If Selection.Find(What:=custID, after:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
    Searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False, searchformat:=False) Is Nothing Then
ws1.Activate
ws2 = Sheets("New")
Else
ws1.Activate
ws2 = Sheets("Changes")
End If

Anyone have any ideas?
Cheers
Ben
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Add Set in front of that line, just like you do with

Code:
Set ws1 = Sheets(ActiveSheet.Name)
 
Upvote 0
*FACEPALM*
I even put the set in the first bit when I first wrote it because I thought it may cause problems if not. Guess I couldnt see the wood for the trees there. Thanks
:D
 
Upvote 0

Forum statistics

Threads
1,214,832
Messages
6,121,850
Members
449,051
Latest member
excelquestion515

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top