How to copy and paste range instead of row?

Question

I am putting together a basic inventory control system and I would like the columns with a time-stamp in the "Checked-Out" column to be pasted into a list on another worksheet. I have successfully copied the correct entire rows, but I would like this to just copy and paste the table rows instead because I have instructions listed in column A that are not relevant for the compiled list. I am new to VBA coding, thanks in advance!

I have named ranges for the two tables called "Inventory_List": Inventory!$I$3:$N$1048576 and "Checked_Out": CheckedOut!$B$3:$G$1048576 as the copy/paste ranges respectively.

Sub testIt()
Dim r As Long, endRow As Long, pasteRowIndex As Long

endRow = 1000 ' of course it's best to retrieve the last used row number via a function
pasteRowIndex = 1

For r = 1 To endRow 'Loop through sheet1 and search for your criteria

    If Cells(r, Columns("N").Column).Value > 0 Then 'Found

            'Copy the current row
            Rows(r).Select
            Selection.Copy

            'Switch to the sheet where you want to paste it & paste
            Sheets("CheckedOut").Select
            Rows(pasteRowIndex + 5).Select
            ActiveSheet.Paste

            'Next time you find a match, it will be pasted in a new row
            pasteRowIndex = pasteRowIndex + 1


           'Switch back to your table & continue to search for your criteria
            Sheets("Inventory").Select
    End If
Next r
End Sub

When I try to reference ranges instead of entire rows, I get "run-time error 1004" because my copy area and paste area aren't the same size, but I am a bit confused because my ranges seem to be the same size. I am pretty sure this is because I am adding the ranges to the incorrect portion of the code.

Side note: you want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Jul 23 '19 at 15:57

Vityata · Answer 1 · 2019-07-23T16:48:41.597

0

Copying and pasting of Excel ranges is quite standard, if you take into account 2 things:

Refer to the ranges correctly with the upper left cell and the lower right cell;
Always, refer to the Parent worksheet.

In the code below, the upper left cell and the lower right cells of the copied and pasted ranges are like this:

.Range(.Cells(count, 1), .Cells(count, "C"))

copyTo.Range(copyTo.Cells(count, 1), copyTo.Cells(count, "C"))

The parent worksheets are always referred. With with for the copyFrom and with explicit writing for copyTo.

Sub TestMe()

    Dim copyFrom As Worksheet
    Dim copyTo As Worksheet

    Set copyFrom = Worksheets(1) 'Or better write the name - Worksheets("CheckedOut")
    Set copyTo = Worksheets(2)

    Dim count As Long
    For count = 1 To 30
        With copyFrom
            If .Cells("N", count) > 0 Then
            .Range(.Cells(count, 1), .Cells(count, "C")).Copy Destination:=copyTo.Range(copyTo.Cells(count, 1), copyTo.Cells(count, "C"))
            End If
        End With
    Next

End Sub

Last, but not least - this is a must read for VBA - How to avoid using Select in Excel VBA

edited Jul 23 '19 at 16:48

answered Jul 23 '19 at 16:17

Vityata

42,633
8
55
100

Thanks for this article! Really helpful for a beginner like me. How would I include the criteria filter for this copy/paste code? – Morgan Hagerman Jul 23 '19 at 16:45
@MorganHagerman - you are welcome. I have added an `If` condition to my answer. – Vityata Jul 23 '19 at 16:49
I attempted to implement your code for my purposes, but I seem to be getting a Cell Mismatch error specifically for the criteria line. These are the modifications I made: Set copyFrom = Worksheets("Inventory") Set copyTo = Worksheets("CheckedOut") Dim count As Long For count = 1 To 30 With copyFrom If .Cells("N", count) > 0 Then .Range(.Cells(count, 1), .Cells(count, "I")).Copy Destination:=copyTo.Range(copyTo.Cells(count, 1), copyTo.Cells(count, "B")) End If End With Next – Morgan Hagerman Jul 23 '19 at 17:24
I think this may be because my time stamp is listed as a date and time, any advice on modifications? – Morgan Hagerman Jul 23 '19 at 17:44

How to copy and paste range instead of row?

1 Answers1