MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying data from two seperate cells on a line - loop this function

Posted by John S on October 09, 2001 10:30 AM

I want to copy data from two cells in one workbook and paste them into another
workbook and then go back to the initial workbook and perform the operation again
but this time on the row below the previous copy. I want to continue until EOF
or until ther is no data left. Can Someone help? Thanks

Posted by Anon on October 09, 2001 11:09 AM

Why do it one line at a time? Why not copy and paste everthing once?

Posted by Re: Copying data from two seperate cells on a line - loop this function on October 09, 2001 11:21 AM

The file I am copying from sometimes might only have one row of data. Other
times it might have 4 or 16. I just want to creat a macro to copy these fields
regardless of the size of the initial file. How would I incriment the cells in
destination workbook so i don't overwrite the previous info.

Posted by Anon on October 09, 2001 11:52 AM

Let's assume you want to copy the data in columns A:B (starting from row 2) in the source workbook to columns A:B in the destination workbook. See if this does what you want :-

Dim wb1 As Workbook, wb2 As Workbook
Dim sSheet As Worksheet, dSheet As Worksheet
Dim sRng As Range, dRng As Range
Set wb1 = Workbooks("whatever1.xls")
Set wb2 = Workbooks("whatever2.xls")
Set sSheet = wb1.Sheets("Sheet1")
Set dSheet = wb2.Sheets("Sheet1")
Set sRng = sSheet.Range(sSheet.Range("A2"), sSheet.Range("B65536").End(xlUp))
Set dRng = dSheet.Range("A65536").End(xlUp).Offset(1, 0)
sRng.Copy dRng