Copy cells between books

Question

Can someone please help me with VBA code.

I am trying to copy 2 ranges of cells between books (both books must be created in advance, since I do not want the code to create a new book "on the fly").

First, I need to copy these ranges - From "Sheet 3" booka.xls Range: cell H5 to the last row in column H with data copy this to "Sheet 1" bookb.xls, starting with Cell B2 so that as much cells dropped in column B

Secondly, I need to copy these ranges - From "Sheet 3" booka.xls Range: cell K5 to the last row in column K with data copy this to "Sheet 1" bookb.xls, starting with Cell D2 so that as much cells dropped in column D

Here is what I still have:

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 

Answer 1

Here is an example of how to make 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 said in the comments, this code above will not work for ranges with spaces, so replace the lastrow line in the code below:

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

Ideally, you can do this in a routine that lists the name of the workbook, the name / number of the worksheet, and the range, as well as the name of the workbook of the destination, the name / number of the worksheet, and the range. Then you do not have to repeat some of the code.

Answer 2

Assuming you have a link to wksFrom and wksTo , this is what 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") 

Answer 3

You can use special cells like Jonsca. However, I usually just look at the cells. I find that this gives me more control over what I copy. The performance is very small. However, I feel that in the office a place that certifies that the data is accurate and complete is a priority. I wrote an answer to a question similar to this one that can be found here:

• fooobar.com/questions/890109 / ...

There is also a small iDevelop demo on how to use custom cells for the same purpose. I think this will help you. Good luck

Update

In response to...

a good start, but it does not copy anything after the first empty cell - connecting lines on June 9 at 5:08

I just wanted to add that the link raised in your comment will be addressed in the above link. Instead of using the .End(xlDown) method, swipe through the cells until you reach the last line you get with .UsedRange.Rows.Count .