Macro bugging when using declared variable "Cl.value" to find worksheet name

[diygail123]

Hi

Can anyone tell me how to stop the code below bugging at the Sheets(Cl.Value).Activate
The filename when I hover over CL.Value is 5837, and this does exist in the worksheet, I have tested by entering "5837" rather than Cl.value and the code runs fine!

Any help much appreciated.

 Sub CopyFltr()

'DECLARE VARIABLES
   Dim Ws As Worksheet
   Dim Cl As Range
   
   Application.ScreenUpdating = False
   Set Ws = Sheets("Import")
   
'FILTER ON EACH VENDOR ON IMPORT TAB
   If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
   With CreateObject("Scripting.dictionary")
     For Each Cl In Ws.Range("b5:b58")                                 'For Each Cl In Ws.Range("B5", Ws.Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Nothing
            Ws.Range("A4:O4").AutoFilter 2, Cl.Value
            Ws.AutoFilter.Range.Copy
            Sheets(Cl.Value).Activate                                                            'Sheets(Cl.Value).Activate
            Range("A6").PasteSpecial
                                                        'Ws.AutoFilter.Range.Copy Sheets(Cl.Value).Range("A4")
         End If
      Next Cl
   End With
   Ws.AutoFilterMode = False
   ActiveWindow.View = xlNormalView
   
   
   MsgBox ("Vendor tabs have now been updated")
   Sheets("Macros").Activate
End Sub

 

[dmt32]

Hi,
try coercing your numeric value to a string

Sheets(CStr(Cl.Value)).Activate

Dave

 

[steve the fish]

Heres how you could do it:

Dim x As String

x = cl.Value
Sheets(x).Activate

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...s-another-separate-worksheet.html#post5181826

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).