Solved

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

Posted on 2014-03-28
6
1,304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Save the day with this special offer from ATEN!

Save 30% on the CV211 using promo code EXPERTS30 now through April 30th. The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 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