Better Way to Copy Rows Out of a Loop?

[beartooth91]

So.....the code below works....as long as the copy from sheet is selected. (If the copy to sheet is selected, while running the procedure; nothing is copied.) This is a common problem I've had when copying between sheets and/or workbooks using With Statements. I need to paste values as some of the data contains formulas or lookups. As I said; the code works, but lots of forum readings say you're not supposed to use 'Activate' and 'Select'.

Sub Copy_Master()
'
'Copies point info from Master IO List Workbook to the Imported sheet in the Master Database Workbook
'
Application.ScreenUpdating = False
Call Clear_Imported
Call Open_Master_IO
'
'Count rows of data in NIC Master IO List worksheet
 Dim a As Long, b As Long, entry As Range
 a = Workbooks("NIC Master IO List.xlsm").Worksheets("NIC Master IO List").Range("B" & Rows.Count).End(xlUp).Row
'
'Determine start row to paste in Imported worksheet of Master Database Workbook
 '
 'Workbooks("NIC Master IO List.xlsm").Worksheets("NIC Master IO List").Range("B11:BP" & a).Copy
 'Workbooks("NIC Master Database.xlsm").Worksheets("Imported").Range("B11:BP" & b).PasteSpecial Paste:=xlPasteValues 'xlPasteAll
 Workbooks("NIC Master IO List.xlsm").Worksheets("NIC Master IO List").Activate
 With Workbooks("NIC Master IO List.xlsm").Worksheets("NIC Master IO List")
   For Each entry In Range("BP11:BP" & a)
     If entry.Value = "Valid" Then
        b = Workbooks("NIC Master Database.xlsm").Worksheets("Imported").Range("B" & Rows.Count).End(xlUp).Row + 1
        entry.EntireRow.Copy 'Destination:=Workbooks("NIC Master Database.xlsm").Worksheets("Imported").Range("A" & b)
        Workbooks("NIC Master Database.xlsm").Worksheets("Imported").Activate
        Workbooks("NIC Master Database.xlsm").Worksheets("Imported").Range("A" & b).PasteSpecial Paste:=xlPasteValues
        Workbooks("NIC Master IO List.xlsm").Worksheets("NIC Master IO List").Activate
     End If
    Next entry
End With
 Application.CutCopyMode = False
 '
End Sub

 

[beartooth91]

The VBA Union method in Excel is designed to combine ranges into a single range. You use Union to combine multiple ranges based on a common criterion as in your case, cell values that match criteria “Valid”
However, Union method cannot combine ranges if one of the ranges does not exist therefore, until object variable rngValid is defined the first time, you cannot include it in Union statement.

To do this, you include a if statement to test each cell in the range

If rngValid Is Nothing Then

And use the set statement to add it to the range

Set rngValid = Cell

Now after the range is defined the first time, you add to the existing range(s) with the Union command to build a single range.

Set rngValid = Union(Cell, rngValid)

You can read more in VBA help file if needed.

Union method is sufficient in many cases but there are, as @Peter_SSs has shown, still much quicker methods to produce required result if needed.

Hope Helpful

Dave

Where is the row coming from to copy? **If I understand your code**, the array - in memory - is only one column BP11:BP & LastRow that have valid entries, not the entire set of rows.....
So I assume it goes back to the copy-from worksheet to copy the rows.....?

 

[dmt32]

Where is the row coming from to copy?

At this point in the code, Union has combined ALL the ranges that match the criteria
Copying the entirerow of the range & pasting to destination sheet is then undertaken in one single action without any need for further interaction with the worksheet which is why code is faster.

If Not rngValid Is Nothing Then
        With wsImported
            LastRow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
            rngValid.EntireRow.Copy
            .Cells(LastRow, 1).PasteSpecial Paste:=xlPasteValues
        End With
    End If

Dave