Avoiding invalid worksheet references

[dleslieca]

I am combining the information from multiple files--one per state, for several states--into a single large table so it's more easily reviewed. Each file has multiple worksheets, named for categories. The category/worksheet names are standardized, but not all worksheets are found in every file.

There is some overlap of items from state to state, so my macro copies key fields from each worksheet and pastes them into the new table and adds a column on the left with the name of the category/worksheet each item came from. It then removes any duplicates to create a list of unique items. Across to the right, the table has two columns for each state, one to show if the item exists there, and the other the date it was added.

In the final step I am using lookup formulas to populate the states columns, using the category/worksheet name from the left-hand column to identify which worksheet to pull from. When a worksheet doesn't exist in a source file, however, this creates an invalid reference.

Is there any way to use VBA to identify which worksheets are in a file so I can use the results in an IF/THEN statement to bypass any lines that would create the invalid references? Or does someone have a more creative way to address this issue?

Thank you!
David

[shg]

Post the relevant portion of the code you're using now.

[dleslieca]

The portion that will currentlyl generate an error is:

Please Login or Register  to view this content.

This is essentially the code that will be run for each state, and it generates an error when the value in column B generates a sheet name for "strSheetNm" that doesn't exist in the current source file being accessed.

I am also looking into if I can use "On Error to skip the formulas, increment the counter and move to the next line in the destination table.

Thank you!

[shg]

Maybe

Please Login or Register  to view this content.

[dleslieca]

Thanks, shg. That's pretty close to what I did, which was add "On Error Resume Next" prior to each formula, so it skips the errors and goes to the next line. That works, but I'd still like to know if there's some way to look at which worksheets exist in a file before executing the next line. I'll mark this as solved.