?
Solved

Adding a second worksheet that has same 'Copy partial row' functionality as Worksheet 1

Posted on 2014-02-28
7
Medium Priority
?
407 Views
Last Modified: 2014-03-01
I am working in Excel 2010. I have attached a sample file that currently copies a partial row from Worksheet 1 to Worksheet 2 based on the criteria outlined below. I need to add Worksheet 3 to this workbook, and want it to have exactly the same functionality as Worksheet 1 — if it meets the same criteria as outlined below, then a partial row is copied into Worksheet 2. So basically Worksheet 2 can be populated by both Worksheet 1 and Worksheet 3.

Criteria and desired result as per sample file attached:
If, in worksheet called "Worksheet 1":
A cell in any row in Column V: is not blank
A cell in the same row as above in Column X: = "Yes"
Then the following cells from the above row are copied and pasted, as values, into the next available blank row in "Worksheet 2", mapped as follows:
Column V value to Column A
Column W value to Column B
Column A value to Column C
Column B value to Column D
Column L value to Column E
Column M value to Column F

Further details:
If there are blank values in any of the cells being copied from Worksheet 1 or Worksheet 3, then the cell in Worksheet 2 would also show as blank.
The values are to be copied over just once into Worksheet 2.
The process would happen automatically anytime the above two conditions are met in Worksheet 1 or Worksheet 3.

Thanks,
Andrea
CopyPartialRow.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
  • 4
  • 3
7 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 2000 total points
ID: 39895441
Remove the code from Sheet1 and paste this code in the ThisWorkbook module.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh Is Sheet2 Then Exit Sub
    
    If Target.EntireRow.Cells(R, 22) <> "" And Target.EntireRow.Cells(R, 24) = "Yes" Then
        CopyRow Target
    End If
    
End Sub

Open in new window


Also, adjust CopyRow and CopyRow2 to take a range as an argument.
Sub CopyRow2(ByVal R As Range, ByVal R2 As Integer)

    Sheet2.Cells(R2, 1) = R.EntireRow.Cells(1, 22)
    Sheet2.Cells(R2, 2) = R.EntireRow.Cells(1, 23)
    Sheet2.Cells(R2, 3) = R.EntireRow.Cells(1, 1)
    Sheet2.Cells(R2, 4) = R.EntireRow.Cells(1, 2)
    Sheet2.Cells(R2, 5) = R.EntireRow.Cells(1, 12)
    Sheet2.Cells(R2, 6) = R.EntireRow.Cells(1, 13)

End Sub

Sub CopyRow(ByVal R As Range)

    If WorksheetFunction.CountA(Sheet2.Cells) = 0 Then
        R2 = 1
    Else
        R2 = Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp).Row + 1
    End If
    
    CopyRow2 R, R2
End Sub

Open in new window

0
 

Author Closing Comment

by:Andreamary
ID: 39895623
Perfect! Thanks very much for the quick response...
0
 

Author Comment

by:Andreamary
ID: 39896039
The initial test of this solution worked, but runtime errors are surfacing after more than a copy of rows are copied over. Should I start a new thread, or can this be addressed in this thread? I've attached the file to illustrate what is happening.
CopyPartialRow-TwoSheetTest.xlsm
0
Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

 
LVL 34

Expert Comment

by:Norie
ID: 39896403
What do you mean 'more than a copy of the rows are copied over'?
0
 

Author Comment

by:Andreamary
ID: 39897253
I'm sorry but I really jumped the gun on closing this question when I did. :-( Please advise how to best rectify this. I'm providing a description of the errors I'm encountering in the file I attached in my previous post. I'm not an expert, so don't hesitate to ask for clarification if my explanations aren't clear. And if you feel that this should be handled as a new thread on my part, just let me know:

1. When entering "Yes" in X1 on either Worksheet 1 or Worksheet 3, run-time error 1004 triggered and line not copied to Worksheet 2 despite meeting criteria

2. Removing "Yes" from Worksheet 1 or Worksheet 3 triggers the line to be copied into Worksheet 2. We need the ability to remove "Yes" without it triggering any action.

3. Reapplying "Yes" to a line that has already been copied over to Worksheet 2 does not trigger the line to be copied over again. (We need the ability to remove "Yes" and reapply "Yes" to lines that have already been copied into Worksheet 2. Worksheet 1 and 3 are lists of chapters in a book, and the same chapter could be revised several times within one cycle, and each time it's going to be revised we copy it again into Worksheet 2.)

4. Removing "Yes" and/or reinserting "Yes" from X1 in either Worksheet 1 or Worksheet 3 triggers run-time error 1004 in Private Sub Workbook_SheetChange: the following line is highlighted in yellow:
 If Target.EntireRow.Cells(R, 22) <> "" And Target.EntireRow.Cells(R, 24) = "Yes" Then

Thanks for your patience,

Andrea
0
 
LVL 34

Expert Comment

by:Norie
ID: 39897297
Oops, the problem is something from the old code that I should have fixed.

Try this for the code in the workbook module.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh Is Sheet2 Then Exit Sub
    
    If Target.EntireRow.Cells(1, 22) <> "" And Target.EntireRow.Cells(1, 24) = "Yes" Then
        Application.EnableEvents = False
        CopyRow Target
        Application.EnableEvents = True
    End If
    
End Sub

Open in new window


I think that fixes all the points you raised but it's probably best if you check again.
0
 

Author Comment

by:Andreamary
ID: 39897466
All good now! I made sure to test it in several ways. Thanks so much...
0

Featured Post

ATEN's HDBaseT Presentation at InfoComm 2017

Hear ATEN Product Manager YT Liang review HDBaseT technology, highlighting ATEN’s latest solutions as they relate to real-world applications during her presentation at the HDBaseT booth at InfoComm 2017.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

765 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