ExcelBanter - How do I access a sheet in another workbook using VBA?

Without knowing more about what you wnat to copy here is a strat for you...

Sub Test()
Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim rngSource As Range
Dim rngDestination As Range

'Set your source
Set wbkSource = ThisWorkbook
Set wksSource = wbkSource.Sheets("Sheet1")
Set rngSource = wksSource.Cells

'Set your destination
On Error GoTo OpenBook
Set wbkDestination = Workbooks("ThatBook.xls")
On Error GoTo 0
Set wksDestination = wbkDestination.Sheets("Sheet1")
Set rngDestination = wksDestination.Range("A1")

'You now have all of your souce and destination objects

rngSource.Copy rngDestination

Exit Sub

OpenBook:
Set wbkDestination = Workbooks.Open("C:\Thatbook.xls")
Resume Next
Exit Sub

End Sub

--
HTH...

Jim Thomlinson

"kurt" wrote:

Greetings everyone!

How do I access a sheet in another workbook?

Currently, I do the cheesie method of referencing it from within the cell,
but I always have to over copy the rows since I don't know when I've reached
the last non-empty row.

I want to be able to write some VBA code that will loop thru the sheet in
one workbook and write the info into a sheet in another work.

Thanks as Always!

Kurt