Excel VBA, Loop, Copy, Loop, Paste, Autofilter, return value, Loop.

[user29383902]

This doesn't seem to work. I'd appreciate any help out there.


I have One (1) Workbook, Sheet1, then other sheets named "1","2"...."15"sheet1 is where my main data is, while the other sheets contains data i want to filter.


so i want to loop through all rows in sheet1 and filter them individually in the other sheets, while looping, i want to make sure the value in cell "G" in sheet1 matches the other worksheet names.


then paste it, and call a function/sub to autofilter (i already have this figured out).


then return the number of rows returned from the filter to cell "H" of sheet1, that was filtered.


i need all this to be a loop.


Kindly help out.

Sub DataAnalysis()

    Sub ArrayBuilder() 'Loops through all rows and copy
    
        myarray = Range("A1:M1000")
        
        For i = 1 To UBound(myarray)
        
            For j = 1 To UBound(myarray, 2)
            
            Debug.Print (myarray(i, j))
            
            Next j
        
        Next i
        
        
     
        Dim wkSht As Worksheet
        For Each wkSht In Sheets
                
        X = Range("G1:G1000")
        
            For i = 1 To UBound(myarray)
        
            For j = 1 To UBound(myarray, 2)
            
            Debug.Print (myarray(i, j))
            
            Next j
        
        Next i
                
        If Sheets("Sheet1").Range(X).Value = wkSht.Name Then  'if value of G in rows (that has been looped through)
        'matches the worksheet name, then paste
        
        Sheets("Sheet1").Rows("2:2").Paste
        Application.CutCopyMode = False
        End If
    Next
    
    Application.Run "'FileX.xls'!FilterX"    ' this activates a macro for autofilter and run it, 

    ' I can also paste the code here but that is not the problem right now
    
    
     X1 = Range("H1:H1000")
        
            For i = 1 To UBound(myarray)
        
            For j = 1 To UBound(myarray, 2)
            
            Debug.Print (myarray(i, j))
            
            Next j
        
        Next i
    
    Sheet1.Range(X1).Value = ws.AutoFilter.Range.Columns(1) ' returns the row count on the filtered data to cell H for every loop

End Sub

End Sub