Copy all used cells from one workbook sheet to another existing workbook sheet

Question

I am trying to copy all the used cells in an excel worksheet from a closed workbook into an already created worksheet in my current workbook using VBA.

Have seen lots of examples on copy the sheet as a new sheet but not just the used contents into an existing sheet.

Thanks! Mike

That answer uses .Copy .PasteSpecial which is possibly the worst possible answer, and the question title itself doesnt refer to "used cells". — WizzleWuzzle

WizzleWuzzle WizzleWuzzle · Accepted Answer · 2017-03-30T23:15:58

Open the source workbook, using: set wb = Workbooks.Open FileName:="fullPathAndFileName.xlsx", UpdateLinks:=0, ReadOnly:=True, AddToMru:=False
Using a reference to the sheet you want to copy, eg., wb.Sheets(1) (refers to the first sheet in the workbook), and a reference to your destination sheet, eg. destWorkSheet run a loop like this:
```
For Each cel in wb.Sheets(1).UsedRange.Cells
    destWorkSheet.Cells(cel.Row, cel.Column) = cel
Next
```

This will copy the values cell by cell to the same location in the destination worksheet as they are in the source worksheet. You probably want to turn calculation off first. There are certainly other ways to do it as well.

You will still need to open the source workbook, but another approach is to do a direct write of Values. You can do this as long as the destination and source ranges are the same size and shape. This subroutine will do it for you:

    Private Sub CopyValuesSourceToDest(src As Range, dest As Range)
    'dest should be one cell, src can be a range of cells
    If dest.Cells.Count <> 1 Then MsgBox "Only pass one cell as the destination.": Exit Sub
    Dim rws As Long, cols As Long, trueDest As Range
    rws = src.Rows.Count
    cols = src.Columns.Count
    Set trueDest = dest.Parent.Range(dest.Cells(1, 1), dest.Cells(1, 1).Offset(rws - 1, cols - 1))
    trueDest.Value = src.Value
    End Sub

You would then call the sub like this:

    CopyValuesSourceToDest sourceSheet.UsedRange, destSheet.Range("B7") 'B7, or whatever

Copy all used cells from one workbook sheet to another existing workbook sheet

1 Answers