Copy cells between workbooks

7
votes

Could someone please help me with some VBA code.

I am trying to copy 2 ranges of cells between workbooks (both workbooks should be created beforehand as i don't want the code to create a new workbook on the fly).

Firstly I need to copy these ranges- From 'Sheet 3' of booka.xls, Range: Cell H5 to the last row in column H with data copy this to 'Sheet 1' of bookb.xls, starting in Cell B2 for as many cells down in the B column

Secondly I need to copy these ranges- From 'Sheet 3' of booka.xls, Range: Cell K5 to the last row in column K with data copy this to 'Sheet 1' of bookb.xls, starting in Cell D2 for as many cells down in the D column

Here is what I have so far:

 Sub CopyDataBetweenBooks()

Dim iRow        As Long
    Dim wksFr       As Worksheet
    Dim wksTo       As Worksheet

    wksFr = "C:\booka.xls"
    wksTo = "C:\bookb.xls"

    Set wksFrom = Workbooks(wksFr).Worksheets("Sheet 3")
    Set wksTo = Workbooks(wksTo).Worksheets("Sheet 1")

    With wksFrom
        For iRow = 1 To 100
            .Range(.Cells(iRow, 8), .Cells(iRow, 9)).Copy wksTo.Cells(iRow, 8)
        Next iRow
    End With

End Sub
3
vbaexcel
Are there blanks in any of the columns? – jonsca
yes, the cell values will vary but it is expected there will be some blanks – trunks
Look up the Workbooks.Open function, as I don't believe you can open the workbooks like you are doing. If there will be blanks interspersed look up the SpecialCells property of a range. Take another crack at it and I'll help you with what I have. – jonsca

3 Answers

1
votes

Assuming you have the reference to wksFrom and wksTo, here is what the code should be

wksFrom.Range(wksFrom.Range("H5"), wksFrom.Range("H5").End(xlDown)).Copy wksTo.Range("B2")
wksFrom.Range(wksFrom.Range("K5"), wksFrom.Range("K5").End(xlDown)).Copy wksTo.Range("D2")
1
votes

Here's an example of how to do one of the columns:

Option Explicit
Sub CopyCells()
    Dim wkbkorigin As Workbook
    Dim wkbkdestination As Workbook
    Dim originsheet As Worksheet
    Dim destsheet As Worksheet
    Dim lastrow As Integer
    Set wkbkorigin = Workbooks.Open("booka.xlsm")
    Set wkbkdestination = Workbooks.Open("bookb.xlsm")
    Set originsheet = wkbkorigin.Worksheets("Sheet3")
    Set destsheet = wkbkdestination.Worksheets("Sheet1")
    lastrow = originsheet.Range("H5").End(xlDown).Row
    originsheet.Range("H5:H" & lastrow).Copy  'I corrected the ranges, as I had the src
    destsheet.Range("B2:B" & (2 + lastrow)).PasteSpecial 'and destination ranges reversed
End Sub

As you have stated in the comments, this code above will not work for ranges with spaces, so substitute in the code below for the lastrow line:

lastrow = originsheet.range("H65536").End(xlUp).Row

Now ideally, you could make this into a subroutine that took in an origin workbook name, worksheet name/number, and range, as well as a destination workbook name, worksheet name/number, and range. Then you wouldn't have to repeat some of the code.

0
votes

You can use special cells like Jonsca has suggested. However, I usually just loop through the cells. I find it gives me more control over what exactly I am copying. There is a very small effect on performance. However, I feel that in the office place, making sure the data is accurate and complete is the priority. I wrote a response to a question similar to this one that can be found here:

There is also a small demonstration by iDevelop on how to use special cells for the same purpose. I think that it will help you. Good luck!

Update

In response to...

good start but it doesn't copy anything after the first blank cell – trunks Jun 9 '11 at 5:08

I just wanted to add that the tutorial in the link above will address the issue brought up in your comment. Instead of using the .End(xlDown) method, loop through the cells until you reach the last row, which you retrieve using .UsedRange.Rows.Count.