Macro to filter on every item in a list, then copy filtered ranges to another, separate, worksheet

[diygail123]

#
Hi, can anyone see why this code is bugging please?

[COLOR=#333333]Sub CopyFltr()[/COLOR]


[COLOR=#333333]'DECLARE VARIABLES[/COLOR]
[COLOR=#333333]Dim Ws As Worksheet[/COLOR]
[COLOR=#333333]Dim Cl As Range[/COLOR]

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]Set Ws = Sheets("Import")[/COLOR]

[COLOR=#333333]'FILTER ON EACH VENDOR ON IMPORT TAB[/COLOR]
[COLOR=#333333]If Ws.AutoFilterMode Then Ws.AutoFilterMode = False[/COLOR]
[COLOR=#333333]With CreateObject("Scripting.dictionary")[/COLOR]
[COLOR=#333333]For Each Cl In Ws.Range("B5", Ws.Range("B" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333]If Not .Exists(Cl.Value) Then[/COLOR]
[COLOR=#333333].Add Cl.Value, Nothing[/COLOR]
[COLOR=#333333]Ws.Range("A4:o4").AutoFilter 2, Cl.Value[/COLOR]
[COLOR=#333333]Ws.AutoFilter.Range.Copy[/COLOR]


[COLOR=#333333]Sheets(Cl.Value).Activate [/COLOR][SIZE=5][COLOR=#333333][COLOR=#ff0000]'BUGGING HERE[/COLOR][/COLOR][/SIZE]
[COLOR=#333333]Range("A6").Paste[/COLOR]
[COLOR=#333333]'Ws.AutoFilter.Range.Copy Sheets(Cl.Value).Range("A4")[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Ws.AutoFilterMode = False[/COLOR]
[COLOR=#333333]ActiveWindow.View = xlNormalView[/COLOR]


[COLOR=#333333]MsgBox ("Vendor tabs have now been updated")[/COLOR]
[COLOR=#333333]Sheets("Macros").Activate[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[KolGuyXcel]

Try: (Using PasteSpecial instead of Paste)

[COLOR=#333333]Sub CopyFltr()
[/COLOR][COLOR=#333333]'DECLARE VARIABLES[/COLOR]
[COLOR=#333333]Dim Ws As Worksheet[/COLOR]
[COLOR=#333333]Dim Cl As Range[/COLOR]

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]Set Ws = Sheets("Import")[/COLOR]

[COLOR=#333333]'FILTER ON EACH VENDOR ON IMPORT TAB[/COLOR]
[COLOR=#333333]If Ws.AutoFilterMode Then Ws.AutoFilterMode = False[/COLOR]
[COLOR=#333333]With CreateObject("Scripting.dictionary")[/COLOR]
[COLOR=#333333]For Each Cl In Ws.Range("B5", Ws.Range("B" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333]If Not .Exists(Cl.Value) Then[/COLOR]
[COLOR=#333333].Add Cl.Value, Nothing[/COLOR]
[COLOR=#333333]Ws.Range("A4:O4").AutoFilter 2, Cl.Value[/COLOR]
[COLOR=#333333]Ws.AutoFilter.Range.Copy[/COLOR]


[COLOR=#333333]Sheets(Cl.Value).Activate[/COLOR]
[COLOR=#333333]Range("A6").Paste[/COLOR][COLOR=#ff0000]Special[/COLOR]
[COLOR=#333333]'Ws.AutoFilter.Range.Copy Sheets(Cl.Value).Range("A4")[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Ws.AutoFilterMode = False[/COLOR]
[COLOR=#333333]ActiveWindow.View = xlNormalView[/COLOR]


[COLOR=#333333]MsgBox ("Vendor tabs have now been updated")[/COLOR]
[COLOR=#333333]Sheets("Macros").Activate[/COLOR]
[COLOR=#333333]End Sub

[/COLOR]

 

[Fluff]

When you get the error, click debug & hover the mouse of the Cl.Value and it should tell you you the value is.
Do you have a sheet with that name?

Also when posting code, click the # icon in the reply window & then paste the code between the tags that appear.

 

[diygail123]

Hi, yes I do have a sheet with that name, it is 5837, and it is the second from last item in my list of vendor codes

 

[diygail123]

I think its something to do with the range Cl is looking at, if I change B5 to B2, which is where the list starts, when I hover over the bug it says empty.

 

[diygail123]

Is it something to do with the sheet name being text, and the list of vendor codes being numbers? 5837 is the first item on the list to be filtered, I can see the macro is actually doing the filter, and doing the copy, but it cant find the sheet named 5837, although it is definately in the workbook.

 

[Fluff]

Ok, change

[COLOR=#333333]Sheets(Cl.Value).Activate [/COLOR][SIZE=5][COLOR=#333333][COLOR=#ff0000]'BUGGING HERE[/COLOR][/COLOR][/SIZE]
[COLOR=#333333]Range("A6").Paste[/COLOR]

to

Sheets(CStr(cl.Value)).Activate
Range("A6").PasteSpecial