Question: How to create an error message and stop work without causing error stop in the code

[figgylynn1023]

I have a procedure I'm working on that uses macros to bring data from multiple reports to one worksheet. Once all the data is there, we want to check for duplicate data sets, both for today's data and comparing today's to yesterday's.

I am working on a single macro that will do the following things:
1. Open the previous day's worksheet
2. Clear any filters and then sort the data numbers in ascending order on the previous day worksheet
3. Go back to the current day worksheet, clear filters and sort data numbers in ascending order
4. Run a vlookup to compare numbers for duplicates between the days.

However, if there is no report from the previous day for some reason, I don't want the "error, subscript out of range" message to pop up and for any of the analysts who will work with this macro to have to reset the code as many of them are not used to working with the code.

Ideally, I would like this macro to be able to search for whether the previous day file exists (I've had some luck with Len(Dir) code with that), but if it doesn't find it, to give a message saying the file does not exist and then shut down the macro and go back to the current day sheet. The message box would allow the analysts to troubleshoot the issue but shutting down the macro would ensure that nothing happens to the current day sheet that shouldn't, and that the analysts don't have to deal with the VBA code to reset the macro.

Any ideas would be much appreciated! Thanks!

 

[kpark91]

Does this tell you what you're looking for?

Sub DoesSheetExist()

'''''''''''''''''''''''''''''''''''''

'Written by www.OzGrid.com



'Test to see if a Worksheet exists.

'''''''''''''''''''''''''''''''''''''



Dim wSheet As Worksheet



	On Error Resume Next

	Set wSheet = Sheets("Sheet1")

		If wSheet Is Nothing Then 'Doesn't exist

			MsgBox "Worksheet does not exist", _

			vbCritical,"OzGrid.com"

			Set wSheet = Nothing

			On Error GoTo 0

		Else 'Does exist

			MsgBox "Sheet 1 does exist", _

                                vbInformation,"OzGrid.com"

			Set wSheet = Nothing

			On Error GoTo 0

		End If

 

[figgylynn1023]

I will give this a try. Thanks.