Moving Data from One Sheet to Another Based on Cell Value

Question

I have a Spreadsheet "upload" I run a macro to compile the data on the sheet. I have a column "D" which attributes the data to client. I want to look for a specific client and automatically move those rows to another worksheet. I have tried this code, but I am making an error "Upload.Range("D1", Upload.Range("D" & Upload.Rows.Count)"

I anticipate future clients information to need be separated from the initial spreadsheet as well.

Any Help would be much appreciated

Sub TransferData()

        Dim ar As Variant
        Dim i As Integer
        Dim lr As Long

ar = Array("3032")

Application.ScreenUpdating = False
Application.DisplayAlerts = False

  For i = 0 To UBound(ar)
         Upload.Range("D1", Upload.Range("D" & Upload.Rows.Count).End(xlUp)).AutoFilter 1, ar(i), 4, , 0
         lr = Upload.Range("D" & Rows.Count).End(xlUp).Row
         If lr > 1 Then
         Upload.Range("A2", Upload.Range("G" & Upload.Rows.Count).End(xlUp)).Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2)
         Upload.Range("A2", Upload.Range("G" & Upload.Rows.Count).End(xlUp)).Delete
         Sheets(ar(i)).Columns.AutoFit
         End If
    Next i
[G1].AutoFilter

Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub

Did you rename the codename of a worksheet Upload? If not, what is Upload? — , Jul 01 '18 at 18:49
I don't see a declaration of Upload, could (should) well be the cause of your error. — JvdV, Jul 01 '18 at 18:53
Yes the name of the worksheet is UPLOAD, 3032 is the other Worksheet. So I am trying to move data from UPLOAD to 3032 — Neil, Jul 01 '18 at 18:54
Is the [worksheet name](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-name-property-excel) or [worksheet codename](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-codename-property-excel) Upload? — , Jul 01 '18 at 19:00
I have always had it as UPLOAD. In Properties Worksheet Name is UPLOAD. (Name) is Sheet1 in properties. I have tried both as Sheet1 and upload and i get the same error — Neil, Jul 01 '18 at 19:06
OK, one last attempt at codename vs. name. When you are looking at the worksheet, what does the tab at the bottom say? — , Jul 01 '18 at 19:33

score 2 · Accepted Answer · 2018-07-01T20:23:37.533

There is a substantial difference between the worksheet Name property and the worksheet Codename property.

While it is possible to change the worksheet's Codename, it isn't a common practice and if you are unsure then it is most likely that you are referring to the worksheet Name property.

Your narrative says nothing about wanting the 'bottom 10 results' but your code uses 4 for the xlBottom10Items operator (see xlAutoFilterOperator enumeration ).

I have no idea what the 3 in Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2) is intended to represent. I would suppose that you meant xlUp which has a numerical value of -4162. (see xlDirection enumeration).

Sub TransferData()

    Dim ar As Variant
    Dim i As Long, lr As Long

    ar = Array("3032")

    ' ... app environment settings removed for brevity

    'reference the filter worksheet properly
    With Worksheets("Upload")
        lr = .Range("D" & Rows.Count).End(xlUp).Row
        If .AutoFilterMode Then .AutoFilterMode = False
        For i = LBound(ar) To UBound(ar)
            'there was no mention of 'bottom 10 items in your narrative but your code shows that option
            With .Range("D1:D" & lr)
                '.AutoFilter field:=1, Criteria1:=ar(i), _
                            Operator:=xlBottom10Items, VisibleDropDown:=False
                .AutoFilter field:=1, Criteria1:=(ar(i)), VisibleDropDown:=False

                With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                    If CBool(Application.Subtotal(103, .Cells)) Then
                        .Offset(0, -3).Resize(, 7).Copy _
                          Destination:=Worksheets(ar(i)).Range("A" & Rows.Count).End(xlUp)(2)
                        Worksheets(ar(i)).Columns.AutoFit
                        .Delete shift:=xlUp
                    End If
                End With
            End With
        Next i
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

    ' ... app environment settings removed for brevity

    MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub

That should get you started. It seems you still have a few decisions o make based on my notes.

_{Application.CutCopyMode = False}

See Should I turn .CutCopyMode back on before exiting my sub procedure?.

Thank you. To be honest I am not sure why the bottom 10 rows was placed in. I am trying to think of why now. Ive change the Autofilter field for the appropriate column, but i seem to still be having an autofilter error when running that segment. — Neil, Jul 01 '18 at 20:16
Removing Operator:=xlBottom10Items is no problem. The code runs fine without it. See edit above. — , Jul 01 '18 at 20:24
That worked to debug that part. I can see all of the filtered information, but it hasnt moved it to the other worksheet do I need to change Destination:=Worksheets(ar(i)).Range("A" & Rows.Count).End(xlUp)(2) Runtime error 1004 returned. — Neil, Jul 01 '18 at 20:46
It would appear you do not have a worksheet named **3032**. Perhaps the worksheet that *looks* like 3032 has a leading or trailing space or something like that. — , Jul 01 '18 at 20:50
That was my thought. So I deleted it, created a new one and re-ran it — Neil, Jul 01 '18 at 20:55

Moving Data from One Sheet to Another Based on Cell Value

1 Answers1