Selectively copy [non-blank cells only] in random column order from one sheet to another

Question

I have a specific need to copy cells from one sheet to another sheet whereby not the entire row is copied. Only data in specific columns in the one sheet needs to be copied to specific columns in the other sheet. Copying does not happen row by row but cell by cell and not in the same order as presented in either sheet . E.g. Copy ROW "A" in Sheet 1 to ROW "D" in Sheet 2.

The code I have here works great except I would like to ONLY copy cells in rows WITH DATA and SKIP the rows with BLANK cells. I would like some help adding a line of code that precedes the copy-functions ("D" to "X", "O" to "Z", etc.) to skip the rows with blank cells.

Sub Test()

Dim i As Long
Dim ii As Long
Dim i3 As Long
Dim LastRow As Long
Dim wb As Workbook
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set wb = ThisWorkbook
Set sht1 = wb.Sheets("DataValues")
Set sht2 = wb.Sheets("BEN")

Sheets("BEN").Select
Range("C192:P220").ClearContents

'Find the last row (in column X) with data in sheet ("DATAValues"). (LIMIT data to COLUMN Z)
LastRow = sht1.Range("Z9:Z37").Find("*", SearchDirection:=xlPrevious).Row

'Start copying data values in "BEN" starting at ROW "192" (due to other data located above)
ii = 192

'This is the beginning of the loop !!!
'Start at row 9 in DATAVALUES to last row with data

For i = 9 To LastRow

    'First activity
    'This is a MUST HAVE for my application

    sht2.Range("D" & ii) = sht1.Range("X" & i).Value
    sht2.Range("O" & ii) = sht1.Range("Z" & i).Value
    sht2.Range("K" & ii) = sht1.Range("AB" & i).Value
    sht2.Range("M" & ii) = sht1.Range("AD" & i).Value

    ii = ii + 1

Next i

End Sub

Skips the rows with blank cells in which sheet and column? I had assumed sht1 column Z — QHarr, Nov 16 '17 at 05:57
Yes that is correct. I would like rows with non-blank cells in column Z in sheet 1 to be copied to sheet 2 in a different column order as presented in the code. Thank you for taking a look at my issue. — DD_LA, Nov 16 '17 at 18:15
I'm unsure what you mean by dfferent column order. Have posted a first answer based on ignoring blanks cells in column Z — QHarr, Nov 16 '17 at 18:17
Yes that is correct ignoring blank cells in column Z in sheet 1. When I say in different order, I meant for example: sht2.Range("D" & ii) = sht1.Range("X" & i).Value The column order in sheet 1 does not correspond with the column order in sheet 2 hence the need for the code to do that. — DD_LA, Nov 16 '17 at 18:20

QHarr · Accepted Answer · 2017-11-17T21:00:48.937

0

Add the following test to skip empty cells (and those resulting in 0 or "")

If Not IsEmpty(sht1.Range("Z" & i)) And sht1.Range("Z" & i) <> 0  And sht1.Range("Z" & i) <> vbNullString

See below

Option Explicit

Sub Test()

    Dim i As Long
    Dim ii As Long
    Dim i3 As Long
    Dim LastRow As Long
    Dim wb As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet

    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("DataValues")
    Set sht2 = wb.Sheets("BEN")

    With Sheets("BEN")

        .Range("C192:P220").ClearContents

        'Find the last row (in column X) with data in sheet ("DATAValues"). (LIMIT data to COLUMN Z)
        LastRow = sht1.Range("Z9:Z37").Find("*", SearchDirection:=xlPrevious).Row
        'Start copying data values in "BEN" starting at ROW "192" (due to other data located above)
        ii = 192

        'This is the beginning of the loop !!!
        'Start at row 9 in DATAVALUES to last row with data

        For i = 9 To LastRow

            'First activity
            'This is a MUST HAVE for my application
            If Not IsEmpty(sht1.Range("Z" & i)) And _
               sht1.Range("Z" & i) <> 0 And _
               sht1.Range("Z" & i) <> vbNullString Then

                sht2.Range("D" & ii) = sht1.Range("X" & i).Value
                sht2.Range("O" & ii) = sht1.Range("Z" & i).Value
                sht2.Range("K" & ii) = sht1.Range("AB" & i).Value
                sht2.Range("M" & ii) = sht1.Range("AD" & i).Value

            ii = ii + 1

            End If

        Next i  

    End With

End Sub

edited Nov 17 '17 at 21:00

answered Nov 16 '17 at 05:53

QHarr

83,427
12
54
101

AWESOME. Thank you. Exactly what I needed. Really made my day. Works like a charm. – DD_LA Nov 16 '17 at 18:31
Well, got excited a little too early. Although some of the cells are actually EMPTY cells, some of the cells have ZERO VALUES or have a **formula** that produces a ZERO ($0.00) or BLANK ("") value. Now these apparently empty cells that have a formula in them, are not excluded. Any thoughts you may have would be really appreciated. Thank you. – DD_LA Nov 17 '17 at 04:19
what cases do you mean to exclude? You don't want to copy cells with formulas in that evaluate to either "", $0.00 or have 0 in column Z? This needs to be specific in order to exclude. – QHarr Nov 17 '17 at 06:21
Yes, I don't want to copy cells that contain formulas that evaluate to either "", $0.00 or have 0 in column Z. That is correct. – DD_LA Nov 17 '17 at 15:09
I have added some additional tests in to the answer. Let me know if that works. It currently will not treat a cell with just ' in as empty. But should handle the cases you described. – QHarr Nov 17 '17 at 15:22
Thank you for your guidance--very much appreciated. I was able to use the code you provided with success. I was also able to figure out to add the "then" statement to close the "if...then..." statement. Because I was not clear on the exclusion of the cells that contain formulas, I removed that line of code and added a line for the "" blank cells exclusion, and that worked. It’s now perfect. I don't know how to thank you for your help an patience. I added the final above. – DD_LA Nov 17 '17 at 20:40
Oops, i put it in the top part where i said add this line but when splitting it out over several lines at the bottom i must have accidentally deleted the Then. Sorry, edited it back in. Well spotted! I had said exclude any formulas rather than just those that evaluated to 0 though those should be excluded by the other condition. Good job on adding the "". Been a long and busy day and missed a few tricks! – QHarr Nov 17 '17 at 20:46
You have been awesome in your help...I thought you had omitted the ...then... on purpose to see if I was paying attention. :) Thanks again. – DD_LA Nov 17 '17 at 20:50
As a matter of interest the test for cells with "" did you have cells with a formula such as an IF( test,........) that returned a "" or something similar? – QHarr Nov 17 '17 at 20:53
Yes that is correct because there are conditions that require no information in the cells in certain instances and where the formula either displays a value or none. I need the none values "" excluded. – DD_LA Nov 17 '17 at 20:57
Edited question with that detail in. You should use <> vbNullstring rather than empty string literal "". i.e. <> vbNullstring rather than <> "". – QHarr Nov 17 '17 at 21:02
vbNullString is faster to assign and process and it takes less memory. See here: https://stackoverflow.com/questions/32435320/is-there-any-difference-between-vbnullstring-and – QHarr Nov 17 '17 at 21:03
Thank you for this 'fine-tuning' comment. Works great! Appreciate your time and your help. – DD_LA Nov 21 '17 at 01:42
I have an additional question about time stamping payments as they are entered. Would I start a new post for this? – DD_LA Nov 21 '17 at 01:43
If it is a new question yes. If it relates to this one then add a link back to this post. – QHarr Nov 21 '17 at 07:41
Ok will do. Thank you again. Hope to carry forward the favor received. – DD_LA Nov 21 '17 at 19:18

Selectively copy [non-blank cells only] in random column order from one sheet to another

1 Answers1

Linked