Excel VBA Copy and paste range

Question

In my sheet I calculate the prime factors for a number, the range is from E6 onwards. I have the following line of code to determine the number of rows.

rCount = Application.WorksheetFunction.CountA(Range("E:E")) - 1

The minus 1 is to exclude the header and get only the range containing numbers. I have also set the range and copied as follows:

Set rgCopy = Range("E6", Selection.End(xlDown))
    Selection.Copy

However, when I try to paste this in a cell it gives me the following error:

Run-time error '1004':
Method 'Range' of object'_Global' failed

Here's the coding I have so far

Dim rgCopy As Range
    Dim rCount As Integer

    Set rgCopy = Range("E6", Selection.End(xlDown))
    Selection.Copy
    rCount = Application.WorksheetFunction.CountA(Range("E:E")) - 1
    lastrow = Cells(Rows.Count, 4).End(xlUp).Row

    Range("E6:" & Selection.End(xlDown)).Copy Destination:=Range("D" & lastrow + 5)

Any assistance would be appreciated.

Hi, there's been countless copy paste range threads here (and everywhere else on the internet...) on daily basis. Please use the search function! — Samuel Hulla
– Samuel Hulla, Commented Jun 22, 2018 at 11:13
Duplicate of VBA paste range (and many more). For example I recently answered one here This also includes few useful mentions as to why you should avoid using ActiveCell and so on. The copy-paste method seems to be fairly similar to what you want to achieve — Samuel Hulla
– Samuel Hulla, Commented Jun 22, 2018 at 11:14
So to clarify: You want to copy data from column E starting from row 6 to the last used row. Then paste this 5 rows below the last used row in column D. Is that correct? — DirtyDeffy
– DirtyDeffy, Commented Jun 22, 2018 at 13:26

DirtyDeffy · Accepted Answer · 2018-06-22 17:48:08Z

1

This works:

Sub test()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row

ActiveSheet.Range("E6:E" & LastRow).Copy

LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

ActiveSheet.Range("D" & LastRow + 5).PasteSpecial xlPasteAll
Application.ScreenUpdating = True
End Sub

answered Jun 22, 2018 at 17:48

DirtyDeffy

5079 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Copy and paste range

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related