Solved

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

Posted on 2014-03-28
6
1,287 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:Andreamary
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39962767
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39962776
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
 

Author Comment

by:Andreamary
ID: 39962787
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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39962807
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
 

Author Closing Comment

by:Andreamary
ID: 39962812
Thanks, Harry, works perfectly!
0
 

Expert Comment

by:Daniele Brunengo
ID: 40682513
And thanks to you both, since this helped me with a similar problem!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question