More Efficient Way to Copy Range From One Workbook to Another???

[jswhip]

I can copy ranges from one workbook to another by going back and forth between them. However, I'm getting the syntax wrong when I would like to cut down on the number of lines. See below:

THIS WORKS:

Dim wrk As Workbook
Dim ws As Worksheet

Set wrk = ActiveWorkbook
Worksheets("ALL Spec Pools").Select
Set ws = ActiveSheet

Set rRange2 = Range(Cells(12, 2), Cells(LastCellRow, 2))
rRange2.Copy

wrk.Activate
rRange2.Copy
Workbooks("Spec Pool Inventory.xlsm").Activate
Worksheets("INVENTORY").Select
Cells(LastCellRow + 1, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I have multiple ranges that I need to copy, thus I would think it would be easier to just reference the source workbook. As I've already named the ranges in the source workbook, I would like to use one line to do the copy. Something like:

wrk.ws.rRange2.Copy (**** THIS SYNTAX FAILS ****)
Cells(LastCellRow + 1, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Thoughts???

 

[TomPC]

I'm not the world's best VBA-ster but seeing as it's only the values you are after, how about this for a quick, reusable sub?

Sub CopyAcrossBooks(cBk As String, cSht As String, cTopRow As Double, cBotRow As Double, cLeftCol As Double, cRightCol As Double, pBk As String, pSht As String, pTopRow As Double, pLeftCol As Double)
    'Work out offsets between copy and paste ranges
    rOffset = cTopRow - pTopRow
    cOffset = cLeftCol - pLeftCol
 
    For RRow = cTopRow To cBotRow
        For CCol = cLeftCol To cRightCol
            Workbooks(pBk).Sheets(pSht).Cells(RRow - rOffset, CCol - cOffset).Value = Workbooks(cBk).Sheets(cSht).Cells(RRow, CCol).Value
        Next CCol
    Next RRow
 
End Sub

The way it works is it reads each cell in your range and then "pastes" it into the other workbook starting at the range you specify. With the variable names, obviously c stands for copy and p stands for paste.

One advantage is that you don't need to 'flick' between books and sheets so much.

You could call it like this:

Sub TestItWorks()
    'Other code including Dims and assignment value for LastCellRow...
    Dim cBk As String
    Dim cSht As String
    Dim cTopRow As Double
    Dim cBotRow As Double
    Dim cLeftCol As Double
    Dim cRightCol As Double
 
    Dim pBk As String
    Dim pSht As String
    Dim pTopRow As Double
    Dim pLeftCol As Double
 
    LastCellRow = 24
 
    'Define Range to Copy
    cBk = ActiveWorkbook.Name
    cSht = "ALL Spec Pools"
    cTopRow = 12
    cBotRow = LastCellRow
    cLeftCol = 2
    cRightCol = 2
 
    'Define cell to paste
    pBk = "Spec Pool Inventory.xlsm"
    pSht = "INVENTORY"
    pTopRow = LastCellRow + 1
    pLeftCol = 3
 
    Call CopyAcrossBooks(cBk, cSht, cTopRow, cBotRow, cLeftCol, cRightCol, pBk, pSht, pTopRow, pLeftCol)
 
End Sub

Hope that helps

Tom