Solved

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

Posted on 2014-03-28
6
1,268 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now