0

I have a workbook already made and it is set up specifically to create histograms on data read in from a separate program. When I pull the data into the workbook, it all goes into one sheet in my workbook. From here I need to split the data apart and sort it into specific tabs based on part number. I have 9 part numbers total and around 25,000 rows of data a day that needs to be sorted. Column A is the date, B is the serial number, C is the part number, D is a machine code, E is the static flow data, and F is a detail. I need to sort by Column C 9 potential part numbers which look like this "'111". "'123" etc with an apostrophe before each number. They are already in that format. The only data that needs to go to the corresponding worksheet is numbers from Column E. This is what I have so far but it doesn't work.

    'For loop to filter through all the available part times and put the data in the correct tab
    For i = 1 To 11
        
        'PartType array is all 9 part types possible
        Worksheets("Paste Data Here").AutoFilter Field:=3, Criteria1:=PartType(i) 'This is where it fails
        Debug.Print ("Filtered")
        Worksheets("Paste Data Here").SpecialCells(xlCellTypeVisible).Select
        
        Selection.Copy
        Debug.Print ("Selected")
        
        'InputRanges is where in each worksheet the data needs to go, this is established
        'in another sub
        'TabList is an array of each worksheet in the same order at the PartType array
        ThisWorkbook.Sheets(TabList(i)).InputRanges(daterange).Select
        ThisWorkbook.Sheets(TabList(i)).InputRanges(daterange).Paste
        Debug.Print ("Pasted")

        
        Application.CutCopyMode = False
        Debug.Print ("i: " & i)
            
        Debug.Print ("PartType(i): " & PartType(i))
    Next i
Tomastep
  • 1
  • 3
  • "it doesn't work." isn't very specific, can you expand on that? On a different note, [avoiding the use of Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code is also a good idea – cybernetic.nomad Jul 14 '21 at 19:00
  • I know the Select function is trash. I just couldn't figure out any other way to do it. The first line in the for loop fails with "Named argument not found" as the explanation. I also know that I'm not pasting JUST the data from column E into each worksheet, its pasting the entire row instead of searching by part type and pasting the corresponding static data. I'm just not sure how to approach this; I've been stuck for 3 days now – Tomastep Jul 14 '21 at 19:09
  • Autofilter works as a method for a range object. For a worksheet, it's a read-only property. You need to supply a range to filter. – Christofer Weber Jul 14 '21 at 21:17

2 Answers2

0

Neither AutoFilter nor SpecialCells works like that for a worksheet. You need to specify some kind of range to apply these methods to.

Dim ws As Worksheet
Set ws = Worksheets("Paste Data Here")
ws.UsedRange.AutoFilter Field:=3, Criteria1:=PartType(i)
ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy ThisWorkbook.Sheets(TabList(i)).InputRanges(DateRange)
Christofer Weber
  • 1,464
  • 1
  • 9
  • 18
  • Is the range for where the data is coming from or where it's supposed to go? I really struggle knowing if I'm communicating with the source or the destination when it comes to vba – Tomastep Jul 15 '21 at 12:25
  • Also, I tried applying the code from the answer and it failed saying " Object doesn't support this property or method" – Tomastep Jul 15 '21 at 13:05
  • Not sure what you mean, you need a range in both cases. Using `.copy` like I did is the reverse of using `=`, so it's `sourceRange.copy destinationRange` Which ro is thring the error? If it's the last, try breaking it up in two rows (after copy) and add `.paste` (like in your code. What is even `.InputRanges` in your code? I can't find such a property or method. – Christofer Weber Jul 15 '21 at 14:42
  • InputRanges was an array of ranges I created where based on what day the source file was created is where that data will go in the destination. I couldn't get the last line you made to work because it wasn't checking if each day being run there was a valid source file. We don't do testing on weekends so if it was trying to open a file from a weekend date it wouldn't exist. If you look at my own answer posted below, this is how I got it to work – Tomastep Jul 15 '21 at 15:47
0
For i = 1 To 11
        Debug.Print ("Searching Part: " & PartType(i))
        
        Dim ws As Worksheet
        Set ws = Worksheets("Paste Data Here")

        ws.AutoFilterMode = False
        Dim rng1 As Range
        Set rng1 = Range("C:C").Find(PartType(i), , xlValues, xlWhole)
        
        If Not rng1 Is Nothing Then
                        
            Dim lastrow1 As Long
            lastrow1 = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            Dim copyRange As Range
            
            Set copyRange = ws.Range("E2:E" & lastrow1)
            
            ws.UsedRange.AutoFilter Field:=3, Criteria1:=PartType(i)
            copyRange.SpecialCells(xlCellTypeVisible).Copy ThisWorkbook.Sheets(TabList(i)).Range(InputRanges(daterange))
        
        End If
Next i
Tomastep
  • 1
  • 3