Copy paste a entire table

Question

I'm new in VBA (also in Stackoverflow) and I need help. I would like to copy a table with many data and paste into another Book (from column D2).

This table goes from A to F and the rows are unknown because they change everyday.

Here is my code that doesn't work :

Windows(" PERIMETRE.xlsx").Activate
    Range("A:F").Select
    Selection.Copy

Windows("OP_COMMERCIALES.xlsm").Activate
    Sheets("DETAIL").Select
    Range("D2").Select
    ActiveSheet.Paste

You can't paste entire columns into row 2... they won't "fit" unless you paste into row 1. Perhaps [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) in your table first. — BigBen, Sep 10 '21 at 13:07
`Application.Workbooks("PERIMETRE.xlsx").Sheets("Sheetname").Range("A1:F10000").Copy Destination:=Application.Workbooks("OP_COMMERCIALES.xlsm").Sheets("DETAIL").Range("D2")` and replace Sheetname with the actual name of the sheet in that workbook you're copying from. And you can put in a dynamic last row like `Range("A1:F" & LastRow)` — Toddleson, Sep 10 '21 at 13:23

Ike · Accepted Answer · 2021-09-10T14:01:04.717

There is no need to select or copy/paste.

First of all I would propose to put all parameters like workbook names etc. as constants to the header of the module. By that it is much easier to fix renamings etc.

Within your copy routine you can define your source-range by using currentregion:

Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only. (https://learn.microsoft.com/en-us/office/vba/api/excel.range.currentregion)

Then define the target range - it depends on the size of the source range.

And finally write the values from source range to target range (no select, no copy, no paste). Important: formatting is not transferred

Option Explicit

Private Const wbSourceName As String = "perimetrie.xlsx"
Private Const numberOfColumnsToCopy As Long = 6     'A - F

Private Const wbTargetName As String = "OP_COMMERCIALES.xlsm"
Private Const wsTargetName As String = "DETAIL"
Private Const cellTarget As String = "D2"



Sub copyTable()

Dim wsSource As Worksheet
Set wsSource = Application.Workbooks(wbSourceName).Worksheets(1)

Dim wsTarget As Worksheet
Set wsTarget = Application.Workbooks(wbTargetName).Worksheets(wsTargetName)


Dim rgSource As Range
'assumption to use currentregion: continous range, no empty rows and columns in area to copy
Set rgSource = wsSource.Range("A1").CurrentRegion

'resize range to the necessary number of columns (A-F)
set rgSource = rgSource.Resize(ColumnSize:=numberOfColumnsToCopy)


Dim rgTarget As Range
Set rgTarget = wsTarget.Range(cellTarget)

'resize rgTarget according to dimensions of rgSource
With rgSource
    set rgTarget = rgTarget.Resize(.Rows.Count, .Columns.Count)
End With


'write values from rgSource to rgTarget - no copy/paste necessary!!!
rgTarget.Value = rgSource.Value


End Sub

Aren't you assuming that OP only wants values? That is not in the question. — BigBen, Sep 10 '21 at 13:53
@BigBen: you are right - this way only values are transferred not formatting. fixed the error (in my original code I wrote everything in one row - did the split only here ...) — Ike, Sep 10 '21 at 13:57
Mercii both of you, it works perfectly ! And thanks to you @Ike my code is more readable, I learnt a lot :) — Larow, Sep 10 '21 at 14:34

Copy paste a entire table

1 Answers1