Solved

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

Posted on 2014-03-28
6
1,281 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Cell Total 3 22
Criteria for Date for DCount 4 26
Clear Filter 8 40
How to get populate a column with a string using excel formula 5 8
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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