Solved How to copy non-adjacent cells using UNION

January 22, 2018 at 00:56:19
Specs: Windows 10
Hi.

Currently the code in the This Workbook module of my workbook copies over entire rows from the
"RD activity review log" worksheet to the "Overview" worksheet when there is a value of "Yes" in column Y. I didn't know how to use the UNION method so was copying over entire rows, then deleting unwanted columns. I've since had to insert columns E:G in the "RD activity review log" worksheet which has messed up the deleting columns workaround.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim c As Range
    Dim J As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    
    Set Source = ActiveWorkbook.Worksheets("RD activity review log")
    Set Target = ActiveWorkbook.Worksheets("Overview")
    
    Application.ScreenUpdating = False
    
    J = 2   'Start copying to row 2 in target sheet where c is Yes in the "High Level Overview" column
    For Each c In Source.Range("Y1:Y1000")  'Do 1000 rows
        If c = "Yes" Then
                 'Target.Rows(c.Row).Application.Union(Range("A" & Source.Row), _
                 Range("B" & Source.Row), Range("C" & Source.Row), _
                 Range("D" & Source.Row), Range(H" & Source.Row), _
                 Range("I" & Source.Row), Range("J" & Source.Row), _
                .Copy Target.Rows(J)
            Source.Rows(c.Row).Copy Target.Rows(J)
            J = J + 1
        End If
    Next c
    
    
    'Delete unwanted columns for Overview sheet - the following code _
    has been commented out because it no longer achieves what I need
    'Worksheets("Overview").Range("H:Y").EntireColumn.Delete
    
    'Sort "Overview" worksheet by Area column and Delivery Date column _
    in ascending order
    Worksheets("Overview").Range("A1:G1000").Sort _
        Key1:=Worksheets("Overview").Range("B1"), _
        Key2:=Worksheets("Overview").Range("G1"), order1:=xlAscending, 
        Header:=xlYes
        
    'Center the text in column A
    Worksheets("Overview").Columns("A:A").HorizontalAlignment = xlCenter
    
    'Sort "Escalations worksheet by Activity Log ID and Date Logged
    Worksheets("Escalations").Range("A1:I1000").Sort _
        Key1:=Worksheets("Escalations").Range("A1"), _
        Key2:=Worksheets("Escalations").Range("B1"), order1:=xlAscending, 
        Header:=xlYes
        
    'Leave the workbook saved on the "RD activity review log" tab
    Worksheets("RD activity review log").Activate

    Application.ScreenUpdating = True

End Sub

What I'd like to do is have the code copy the row values from worksheet "RD activity review log" columns A:D and H:J when there is a value of "Yes" in column Y, and paste them into columns A:G on the "Overview" worksheet from row 2 onwards. This can still be done as a Workbook BeforeSave event.

I hope that all makes sense, but let me know if you need anything clarifying.

I did have a go at using the UNION method which didn't work and I've commented it out just so you can see what I tried. I've also commented out the column delete code as it currently isn't helping since inserting the three columns.

Many thanks


See More: How to copy non-adjacent cells using UNION


#1
January 22, 2018 at 11:04:03
✔ Best Answer
First, I assume that "Do 1000 rows" is just an over estimation of how much data you might have to copy to ensure that you get it all.

Why not determine exactly how much data you have to copy and not waste VBA time and resources testing 1000 rows? Pick a column, e.g. A, that has data all the way to the bottom of your data set and let VBA determine how many Rows to check.

lastRw = Range("A" & Rows.Count).End(xlUp).Row

As far as your copying question, why not just copy columns A:D,H:J to A by building the range? I'm not sure I see any need for the use of Union.

I've included 2 different ways to accomplish your goal. One method loops through column Y similar to your method, the other uses .Find to go directly to the "Yes" entries without checking every cell.

Sub CopyPartialRows_Loop()
Set Source = ActiveWorkbook.Worksheets("RD activity review log")
Set Dest = ActiveWorkbook.Worksheets("Overview")

'Determine length of Data Set
  lastRw = Source.Range("A" & Rows.Count).End(xlUp).Row

'Initiate target Row counter
    J = 2

'Loop though Data Set, Copy A:D, H:J when Column Y = Yes
     For nxtRw = 1 To lastRw
      If Source.Range("Y" & nxtRw) = "Yes" Then
         Source.Range("A" & nxtRw & ":D" & nxtRw & _
                      ",H" & nxtRw & ":J" & nxtRw).Copy _
           Dest.Range("A" & J)

'Increment Row counter
           J = J + 1
      End If
     Next
End Sub

========================================================================

Sub CopyPartialRows_Find()
Set Source = ActiveWorkbook.Worksheets("RD activity review log")
Set Dest = ActiveWorkbook.Worksheets("Overview")

'Determine length of Data Set
  lastRw = Source.Range("A" & Rows.Count).End(xlUp).Row

'Initiate target Row counter
    J = 2
    
'Find Yes In Column Y
 With Source.Columns(25)
    Set y = .Find("Yes", LookIn:=xlValues, lookat:=xlWhole, after:=Range("Y" & Rows.Count))
    If Not y Is Nothing Then
        firstAddress = y.Address
         Do
           Source.Range("A" & y.Row & ":D" & y.Row & _
                        ",H" & y.Row & ":J" & y.Row).Copy _
           Dest.Range("A" & J)
'Increment Row counter
           J = J + 1
            Set y = .FindNext(y)
         Loop While Not y Is Nothing And y.Address <> firstAddress
    End If
 End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
January 22, 2018 at 12:08:11
BTW...

I changed Target to Dest in my code.

The reason for this is that Target is sort of an assigned string that is used to designate the Target cell in a Worksheet_Change, WorksheetSelection_Change, etc. macro.

I prefer to use Source and Dest (Destination) when referring to the "From" and To" sheets.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#3
January 23, 2018 at 01:34:05
Thank you DerbyDad03

I went with your .Find solution as I want to play with using this code more often. Can I ask just out of curiosity, if I needed to copy a single column in the range, e.g. A:D, F, H:J, would I still build the range in the same way? Something like:

Source.Range("A" & y.Row & ":D" & y.Row & ",F" & y.Row & ",H" & y.Row _
& ":J" & y.Row).Copy Dest.Range("A" & J)

If you don't have time to answer, then thank you so much for your help again. It's really appreciated.

All the best.


Report •

Related Solutions

#4
January 23, 2018 at 05:49:21
There are lots of way to build a string to represent a range. Your syntax should work.

I'm not sure if you are familiar with the VBA Watch window, but if not, you should be. By using the Watch window you could set a Watch on your "range string" to make sure it returns the correct Address.

e.g. Set a Watch for:

Range("A" & y.Row & ":D" & y.Row & ",F" & y.Row & ",H" & y.Row & ":J" & y.Row).Address

As you Single Step through the code (F8) any variables in the Watch window will display their current value.

See here for more info on VBA debugging techniques:

http://www.computing.net/howtos/sho...

BTW...Here's an important thing to be aware of when using the .Find method in VBA:

.Find in VBA and the Find feature in Excel are tightly connected. Any options chosen in VBA (e.g. MatchCase = True) will be retained by the Find feature after the macro has been run. VBA will actually set the Check Box for those options in the Find dialog box. The reverse is also true. Any options chosen by the user in the Find dialog box will be used by VBA unless the code specifically changes it.

When using .FIND, you need to make sure that you set the options that you want or you could run into issues. On the flip side, the original user settings are lost after the VBA code runs, assuming you changed something. There may be a way to capture the current user settings and then set them back before the macro finishes, but I haven't looked into that. Resetting them to the default setting is fairly easy. All you need to do is .Find "" (Nothing) with all of the options set to their defaults. This will clear the search string in the Find dialog box and set all options to the defaults.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#5
January 23, 2018 at 05:56:00
That's great advice. Thanks for the note of caution.

If there was a Nobel Prize for VBA advice I'd nominate you! :-)

All the best.


Report •

Ask Question