Return to Answer

According to Durgess you can just get both values and formats with a single argument and avoid clipboard by not copy and paste-ing. Trust me, that's slow!

According to Durgess you can just get both values and formats with a single argument and avoid clipboard by not copy and paste-ing. Trust me, that's slow!

As for an example as to why you beef up your naming -

SupName1 = SelectedSheets(0)
SupName2 = SelectedSheets(1)
SupName3 = SelectedSheets(2)

I'm not sure what's going on here at all. I take it we're getting names of something from the SelectedSheets property, but I don't know why, how or what the input should look like.

Dim strGUID As String, theRef As Variant
'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"

Since it never changes.

If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If

Also you have GoTo ExitSub but I don't see that label anywhere.

Dim strGUID As String, theRef As Variant
'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"

Since it never changes. You can also apply this logic to places like this -

Set ws3 = ActiveWorkbook.Sheets("ANALYSIS - Lead Times")
Set ws4 = ActiveWorkbook.Sheets("ANALYSIS - prices")

With

Const LEAD_SHEET_NAME As String = "ANALYSIS - Lead Times"
Const PRICES_SHEET_NAME As String = "ANALYSIS - prices"
Set ws3 = ActiveWorkbook.Sheets(LEAD_SHEET_NAME)
Set ws4 = ActiveWorkbook.Sheets(PRICES_SHEET_NAME)

The reason to do this is so that you can keep track of things that don't change often more easily and be able to change them without searching everywhere for them.

If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If

###Notes

You have GoTo ExitSub but I don't see that label anywhere.

Your spacing for your With block got messed up

 With ThisWorkbook
 Set ws5 = .Sheets.Add(After:=.Sheets(.Sheets.Count))
 ws5.Name = "Transposition"
'Define the names of the selected worksheets as supplier names
 End With