How can I copy and paste a dynamic range in Excel?

Question

I am trying to copy a dynamic range from a table and copy it to another workbook but am having issues copying the dynamic matrix that is created.

I've tried multiple lines of code but with similar results. I've stepped through the code one line at a time, and the lastRow and lastColumn functions return the expected values. The issue arises when I try to select the matrix and copy it. I feel like there's a simple fix and I'm missing something.

Workbooks.Open Filename:=OOBmap

'Copies AM open order book and pastes it into master spreadsheet

Set startCell = ActiveSheet.Range("A1")
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
ActiveSheet.Range(Cells(1, lastRow), Cells(1, lastColumn)).Select
Selection.Copy

score 1 · Accepted Answer · answered May 02 '19 at 14:28

1

If you want the whole range from A1 to bottom right use this

ActiveSheet.Range(Cells(1, 1), Cells(lastrow, lastColumn)).Copy

If you just want the last column use

ActiveSheet.Range(Cells(1, lastColumn), Cells(lastrow, lastColumn)).Copy

Cells syntax is row then column, you were mixing them up.

Plus you don't need to Select before copying.

Obviously, copying alone won't do anything, you then need to paste somewhere.

answered May 02 '19 at 14:28

SJR

22,986
6
18
26

Tried using the first line of code posted but it's still only copying cell A1 and not the rest of the data. Any idea why that's happening? – radeg May 02 '19 at 15:23
You need to check the values of your two variables. Sounds like the active sheet is empty – SJR May 02 '19 at 16:25

score 0 · Answer 2 · answered May 02 '19 at 14:29

You needed the destination range to paste:

Option Explicit
Sub CopyPaste()

    Dim wsSource As Worksheet, wbDestination As Workbook, wsDestination As Worksheet, LastRow As Long, lastColumn As Long

    Set wbDestination = Workbooks.Open(Filename:=OOBmap, ReadOnly:=True)
    Set wsSource = ThisWorkbook.Sheets("Name") 'change Name for the name of the worksheet you are copying from
    Set wsDestination = wbDestination.Sheets("Name") 'change Name for the name of the worksheet you are copying to

'Copies AM open order book and pastes it into master spreadsheet
    With wsSource
        LastRow = .Cells(.Count, 1).End(xlUp).Row
        lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range(Cells(1, LastRow), Cells(1, lastColumn)).Copy wsDestination.Range("A1") 'here the range were u want to paste
    End With

End Sub

How can I copy and paste a dynamic range in Excel?

2 Answers2