Change VBA code so partial row to be copied to another sheet if cell not blank

Attached is a sample of the current VBA code I am using, which copies over a partial row to another sheet if a cell = "Yes". I would like to change the code so that the partial row will be copied over to another sheet if the cell is not blank.

Thanks!
Andrea
CopyPartialRow-Example.xlsm
AndreamaryAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Harry LeeConnect With a Mentor Commented:
Sorry Andrea!

It's my bad. I have overlooked by security setting to prevent it from functioning properly.

I have retested your vba.

In order to trigger the macro to function if the test cell is not blank instead of the test cell being "Yes", you have 2 places to change.

Open VBA Editor by pressing Alt-F11. Then in the Project Explorer (Ctrl-R), find:
1) ThisWorkbook, and double click to open it.
    Change: If Target.EntireRow.Cells(1, 24) = "Yes" Then
    To: If Target.EntireRow.Cells(1, 24) <> "" Then

2) Module1, and double click to open it.
    Change: If Sheet1.Cells(I, 24) = "Yes" Then
    To: If Sheet1.Cells(I, 24) <> "" Then

That should do it.
0
 
Harry LeeCommented:
Andreamary,

You vba macro is not even functional. How can you be using it at the moment? Are you sure you have included the full macro with your sample?
0
 
Harry LeeCommented:
Andrea,

If your vba is actually working, you have to change the Line 3 of the following code
Sub InitCopyRow()
    For I = 1 To Sheet1.UsedRange.Rows.Count
        If Sheet1.Cells(I, 24) = "Yes" Then
            CopyRow (I)
        End If
    Next
End Sub

Open in new window

From:
If Sheet1.Cells(I,24) = "Yes" Then

Open in new window

To:
If Sheet1.Cells(I,24) <> "" Then

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
AndreamaryAuthor Commented:
Hi Harry,

That's odd, as it works on my system. On Worksheet 1, with 4 lines of sample text, if I enter "Yes" in Column X,  a partial row is copied to Worksheet 2. Removing and re-entering "Yes" again in Column X will trigger the row to copy over a second time.

Andrea
0
 
AndreamaryAuthor Commented:
Thanks, Harry, works perfectly!
0
 
Daniele BrunengoIT Consultant, Web DesignerCommented:
And thanks to you both, since this helped me with a similar problem!
0
All Courses

From novice to tech pro — start learning today.