Copy rows to other sheets based on value in column Solved/Closed

Question

Hello, 

System Configuration: Windows / Chrome 75.0.3770.142
I have copy and pasted the code used to solve the problem for the question which began this thread.  
I have 6 tabs into which I want to copy data based on inputs in column E.
I have named my tabs, but I can't see that this should be a problem.
code is below and data 
Sub TransferData()

        Dim ar As Variant
        Dim i As Integer
        Dim lr As Long
        
ar = Array("1 ADJUVANT", "2 2,4-D MANY CAS #", "16 ALDICARB", "60 CHLOROTHALONIL", "97 ENDOTHALL", "146 GLYPHOSATE")
  
Application.ScreenUpdating = False
Application.DisplayAlerts = False

  For i = 0 To UBound(ar)
 
   'problem is in this row. To be fair, I don't know what the 7,,  0 means

testingData.Range("e2", testingData.Range("e" & testingData.Rows.Count).End(xlUp)).AutoFilter 1, ar(i), 7, , 0
         lr = testingData.Range("e" & Rows.Count).End(xlUp).Row
         If lr > 1 Then
         testingData.Range("e2", testingData.Range("e" & testingData.Rows.Count).End(xlUp)).EntireRow.Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2)
         Sheets(ar(i)).Columns.AutoFit
         End If
    Next i
testindData.[e2].AutoFilter

Is there a way for me to show you a portion of my data?

ac3mark · Answer

You range is not constucted properly.

A range is defined as in Range ("A1:J10")

If you were to replace the variable that are making the range, you would notice that the range is missing thedelimitor of  ":".

So, replace the first comma with this

&":"&

To make it

Testingdata.Range ("e2" & ":" & .........


 give that a try.

ac3mark · Answer

You need another closing ) to define the range!  And you need to apply the same range construction to thw second range of e.

ac3mark · Answer

I see it now!

OK, I added in two lines:

Dim testingData As WorksheetSet testingData = ThisWorkbook.Sheets("testingData")

So that the sheet is initialized.  It runs for me.  If you get an error, the tab names are incorrect.

Sub TransferData()Dim ar As VariantDim i As IntegerDim lr As Long'I added this VVVVVVVVVVVVVDim testingData As WorksheetSet testingData = ThisWorkbook.Sheets("testingData")'make certain there is a sheet called "testingData"'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ar = Array("1 ADJUVANT", "2 2,4-D MANY CAS #", "16 ALDICARB", "60 CHLOROTHALONIL", "97 ENDOTHALL", "146 GLYPHOSATE")'make certain your tabs are named exaclty as the array above!Application.ScreenUpdating = FalseApplication.DisplayAlerts = FalseFor i = 0 To UBound(ar)testingData.Range("e2", ThisWorkbook.Worksheets("testingData").Range("e" & ThisWorkbook.Worksheets("testingData").Rows.Count).End(xlUp)).AutoFilter 1, ar(i), 7, , 0lr = testingData.Range("e" & Rows.Count).End(xlUp).RowIf lr > 1 ThentestingData.Range("e2", testingData.Range("e" & testingData.Rows.Count).End(xlUp)).EntireRow.Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2)Sheets(ar(i)).Columns.AutoFitEnd IfNext itestingData.[e2].AutoFilterEnd Sub

vcoolio · Answer

Hello MsBluebird,

Further to what ac3mark has shown you, the code could be trimmed down a little as follows:-


Sub TransferData()

Dim ar As Variant, i As Integer, sh As Worksheet
        Dim ws As Worksheet: Set ws = Sheets("Testing Data") '---->Sets a value to the variable ws.
        
        ar = Array("1 ADJUVANT", "2 2,4-D MANY CAS #", "16 ALDICARB", _
        "60 CHLOROTHALONIL", "97 ENDOTHALL", "146 GLYPHOSATE")

Application.ScreenUpdating = False

For i = 0 To UBound(ar) '---->Looping from the bottom of the array to the top.

Set sh = Sheets(ar(i)) '---->Sets a value to the variable sh.
        sh.UsedRange.Offset(1).Clear '---->Clears the destination sheets of data (except headings)ready for new data.
        
        With ws.[A1].CurrentRegion
                .AutoFilter 5, ar(i)  'Filters the data set on Column E to find the array values.
                .Offset(1).EntireRow.Copy sh.Range("A" & Rows.Count).End(3)(2) '---->Copy/paste to the destination sheets.
                .AutoFilter '---->Turns the autofilter off.
                sh.Columns.AutoFit '---->Autofits the column widths in the destination sheets.
        End With '---->Closes off the With statement.
Next i '---->Moves onto the next array item.

Application.ScreenUpdating = True

End Sub

I've added in some notes (in green font) to help you understand what the code does.

From your opening post:-

problem is in this row. To be fair, I don't know what the 7,, 0 means 

The '7' is the enumeration for xlFilterValues. The '0' is the enumeration for the filter visible dropdowns to remain off (0=False).

Check that all spelling/punctuation (inc. spaces) is exactly the same so that tab names match exactly to the array values. Also ensure that the "Testing Data" sheet name matches exactly to its variable in the code.

I've attached a little example at the following link just to show you how this code works:-

http://ge.tt./678jGGx2

Click on the "RUN" button to see it work.

I hope that this helps.

Cheerio,
vcoolio.

Copy rows to other sheets based on value in column

4 responses

Similar discussions

Subscribe To Our Newsletter!