Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a sheet in another workbook using VBA?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking workbook and range protected Excel sheet to Access | Excel Discussion (Misc queries) | |||
Read-only access by SHEET (not workbook). Is it possible? | Excel Worksheet Functions | |||
link Access workbook to Excel workbook | Excel Discussion (Misc queries) | |||
Using ADO to access another workbook | Excel Programming | |||
Can't access to a WorkBook | Excel Programming |