Need Flexible Code - Users keep changing Sheet names...

[hellfire45]

Hey guys. So I have an automation that processes a lot of manual files. It's admittedly a bad process.

On one file the user keeps submitting the data with one of the worksheet names with a space at the end. They either submit it as "Shipment" or "Shipment " My code as seen below does not handle this issue.

Set source_sheet = Workbooks(strFileName).Worksheets("Shipment")

I need coding that will process the sheet with or without the space. I was hoping I would use a wildcard & "*" but I don't think this works.

Also, I saw people suggesting to use

 .worksheets(1)

This workbook has multiple worksheets and I wouldn't put it past the user to re-order them. So this doesn't help.

Any suggestions? Talking to them is not an option. Thanks.

 

[RoryA]

If those are the only choices, I'd use a simple brute force approach:

On Error Resume Next
Set source_sheet = Workbooks(strFileName).Worksheets("Shipment")
if source_sheet is nothing then Set source_sheet = Workbooks(strFileName).Worksheets("Shipment ")
on error goto 0

 

[hellfire45]

Thanks. So I already have an on ERROR Resume Next in the code because this automation is rifling through a folder and processing like 100 different files...

So when it can't find the initial Sheet name, its going to close and skip the workbook. Is there anyway to get the

[COLOR=#333333]Set source_sheet = Workbooks(strFileName).Worksheets("Shipment")[/COLOR]

line to not throw an error and do some kind of like an IFERROR( try other sheet name) type of operation?

 

[RoryA]

If you've already got an On Error Resume Next then you don't need the error handling I added (though it's really bad practice to just stick OERN at the start of your code!). All you need is:

Set source_sheet = Workbooks(strFileName).Worksheets("Shipment")
if source_sheet is nothing then Set source_sheet = Workbooks(strFileName).Worksheets("Shipment ")

 

[Fluff]

Another option, this looks for any sheet name containing "shipment"

   Dim Ws As Worksheet
   For Each Ws In Workbooks(strFileName).Worksheets
      If Ws.Name Like "*Shipment*" Then
         Set source_sheet = Ws
         Exit For
      End If
   Next Ws

 

[hellfire45]

Thank you Fluff. This worked well. Exactly what I wanted!

 

[Fluff]

Glad we could help & thanks for the feedback