0

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

0m3r
  • 12,286
  • 15
  • 35
  • 71
Mike Wojcik
  • 11
  • 1
  • 1
  • 3
  • http://stackoverflow.com/a/43114605/4539709 – 0m3r Mar 31 '17 at 00:45
  • 4
    Possible duplicate of [Copy everything in a worksheet vba](http://stackoverflow.com/questions/43114161/copy-everything-in-a-worksheet-vba) – 0m3r Mar 31 '17 at 00:46
  • That answer uses `.Copy .PasteSpecial` which is possibly the worst possible answer, and the question title itself doesnt refer to "used cells". – WizzleWuzzle Mar 31 '17 at 21:30

1 Answers1

0
  1. Open the source workbook, using: set wb = Workbooks.Open FileName:="fullPathAndFileName.xlsx", UpdateLinks:=0, ReadOnly:=True, AddToMru:=False

  2. 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
WizzleWuzzle
  • 145
  • 1
  • 13