Can anyone troubleshoot my code?

[outlawdevil]

    Sub CopyFilteredData()
    Dim sName1 As String
    Dim sName2 As String
    Dim sName3 As String
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim Last As Long
    
    ''
    ''
    sName1 = "Blue"
    sName2 = "Red"
    sName3 = "white"
    Set sh1 = Sheets("Data Tab")
    Set sh2 = Sheets("Upload Tab")
    Last = sh1.Cells(Rows.Count, "C").End(xlUp).Row
    ''
    'Filter rows based on Name which is in column 3
    sh1.Range("A1:C" & Last).AutoFilter
    sh1.Range("A1:C" & Last).AutoFilter Field:=3, Criteria1:=sName
    ''
    'Copy filtered table and paste it in Destination cell.
    sh1.Range("A2:C" & Last).SpecialCells(xlCellTypeVisible).Copy
    sh2.Range("B4").PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
    ''
    'Remove filter that was applied.
    ActiveSheet.AutoFilterMode = False
    ''
    End Sub

Trying to copy the "Blue" filtered data to my upload tab range but got runtime error 1004 at this line. sh1.Range("A2:C" & Last).SpecialCells(xlCellTypeVisible).Copy Thanks.

My goal is to try to filter by specific critieria, copy and paste to designated area. Screen prints for reference. thanks.

 

[outlawdevil]

Is there anything to copy? You're filtering on sName but that's not a Variable.

I totally missed that part, my code would have worked. Thanks for point it out!

 

[Michael M]

will I be able to modify if I add more color to the item arrays and maybe other color will only need 50 rows in the future? so keeping 110 and then 50 for others.

Yes, you can add more colors into the array line

items = Array("Blue", "Red", "White")

and you can modify your number of row spacings here..

 last2 = last2 + 110

 

[outlawdevil]

Yes, you can add more colors into the array line

items = Array("Blue", "Red", "White")

and you can modify your number of row spacings here..

 last2 = last2 + 110

thank you!

 

[Michael M]

thank you!

Glad to help. .??