Avoiding copy/paste, selection to transfer data in VBA

[ItalianPlatinum]

Looking for a way to transfer a set of data over to another sheet after i set a filter without copy, selecting or pasting.

1. Sheet 1: filtered to not show blanks
   1. Want to transfer the data left showing
2. Sheet 2: Where I want to transfer the data to
3. Columns match up like this:
   1. Column A to Column A13 down
   2. Column I to Column B13 down
   3. Column J to Column C13 down
   4. so on and so on.....

 

[Peter_SSs]

A text True here is the snip of the code if it matters

    .Range("P3:P" & lr).Formula = "=OR(I3<>"""",K3<>"""")"

That looks like a logical TRUE/FALSE result in column P to me.

Try making this change in your code

If UCase(vRows(i, 1)) = "TRUE" And UCase(vRows(i, 2)) = "YES" Then
If vRows(i, 1) And UCase(vRows(i, 2)) = "YES" Then

 

[ItalianPlatinum]

But then I change it to a value using this

    .Range("I3:Q" & lr).Value = .Range("I3:Q" & lr).Value

so would that make it a text?

Ok I tried that code and still same error at the same spot

 

[Peter_SSs]

But then I change it to a value using this

.Range("I3:Q" & lr).Value = .Range("I3:Q" & lr).Value

so would that make it a text?

No

Ok I tried that code and still same error at the same spot

Run it again and when the error occurs, click Debug then hover over the variable k and tell us what value appear in the pop-up.

 

[ItalianPlatinum]

See below:

While hovering over vrows says subscript out of range? Take a look. same for (i, 2)

 

[Peter_SSs]

Thanks. k=0 (& the other things that you reported) indicates that no rows were found to meet your conditions & therefore the error makes sense as we cannot resize a range to be zero rows.
It looks like vRows may not be being populated how you think it is/should be.

Would need a small set of sample data with at least one row that you expect to meet the conditions to investigate further.
Also the other code that declares/defines WsSP1 and WsDIST

 

[ItalianPlatinum]

Thanks Peter you led me in the right direction. Some columns were inadvertently empty (Column I, J, L) therefore the conditions were met but there was nothing to transfer. the formulas were commented out during this testing and not put back in. I have since corrected that and it is operating. So 8 rows as expected were transferred over.

In my post #44 and the thread that was closed. If i applied this transfer code for another sheet (same layout) to the same destination. how would i go about transferring data to the next available row in WsDist? the first code works because the sheet is empty. after the first run there is data in their so the 2nd run has a variable to deal with

 

[Peter_SSs]

how would i go about transferring data to the next available row in WsDist?

In WsDist, for every row is there a value in column A? That is can we use column A (or some other fixed column) to determine the last row that contains data?

If not, can you confirm that it is columns A:H in WsDist that would need to be checked to find the last used row?

 

[ItalianPlatinum]

Yes Column A would always have data if there was data transferred the only columns that could be empty would be (F-J) while (A-E) will have columns populated

 

[Peter_SSs]

Then try something like this

Dim nrDist As Long '<- Put this at the top with other declarations

WsDIST.Range("A2").Resize(k, UBound(vCols) + 1).Value = Application.Index(.Cells.Value, Application.Index(vRows, 0, 1), vCols)

nrDist = WsDIST.Range("A" & Rows.Count).End(xlUp).Row + 1
WsDIST.Range("A" & nrDist).Resize(k, UBound(vCols) + 1).Value = Application.Index(.Cells.Value, Application.Index(vRows, 0, 1), vCols)

 

[ItalianPlatinum]

Thanks I think the

("A" & nr)

is suppose to be

("A" & nrDist)

right? ill make that modification

Not sure why I am struggling with modifying the 2nd iteration. Below looks like it would work but i get runtime 1004 at here

        WsDIST.Range("A" & nrDist).Resize(k, UBound(vCols) + 1).Value = Application.Index(.Cells.Value, Application.Index(vRows, 0, 1), vCols)

. I just want to remove the 2 matching conditions to 1. With only column P as a condition. Do you see why it wouldn't work? when i hover over k it says 0 and subscript out of range on the matching criteria

If UCase(vRows(i, 1)) = "Yes" Then

. Column P is the same as the prior so True/False. there should be 46 records to transfer. the data set will potentially and naturally have empty cells in Column I and J if column P is True is that causing problems?

'transfer CD data over to Compare tab
  vCols = Array(1, 2, 3, 4, 8, 9, 10, 11) '<- Columns of interest in specified order
  With WsSP
    With .Range("A1:P" & .Range("H" & rows.count).End(xlUp).row)
        If .rows.count > 2 Then
            vRows = Application.Index(.Cells, Evaluate("row(1:" & .rows.count & ")"), Array(16))
            For i = 3 To UBound(vRows)
                If UCase(vRows(i, 1)) = "Yes" Then
                    k = k + 1
                    vRows(k, 1) = i
        End If
      Next i
        nrDist = WsDIST.Range("A" & rows.count).End(xlUp).row + 1
        WsDIST.Range("A" & nrDist).Resize(k, UBound(vCols) + 1).Value = Application.Index(.Cells.Value, Application.Index(vRows, 0, 1), vCols)
      Else
        MsgBox "No data to transfer"
      End If
   End With
  End With