Referencing worksheet in another workbook

jbfluor

New Member
Joined
Sep 27, 2006
Messages
6
hi all,

Ok i think this should be a fairly simple question for you lot.

I need to know how to reference another workbook

ie WorkbookA - SheetA = WorkbookB - SheetB so far i have this:

([WorkbooknameA.xls]. Worksheets ("WorksheetnameA")=([WorkbooknameB.xls]. Worksheets ("WorksheetnameB")

I hope i have made this clear as to what im looking for here.Heres my actual code

LastRow = Workbooks("Prelim 26 Sept 2006.xls").Worksheets("Prelim 19 Sept 2006").UsedRange.Rows.Count
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
the format is
[workbookname]sheetname!A1:B1

workbookname can be anything from "Workbook2" (even an unsaved workbook works) to 'C:\test.xls', if the workbook is already open you don't have to add the .xls but you can use the name shown in the title bar instead.
 

jbfluor

New Member
Joined
Sep 27, 2006
Messages
6
thats what i thought it was but its coming up with a syntax error.

I have tried:

LastRow = [Prelim 19 Sept 2006.xls]"Prelim 19 Sept 2006"!UsedRange.Rows.Count

and

LastRow = [Prelim 19 Sept 2006.xls]Prelim 19 Sept 2006!UsedRange.Rows.Count

and neither works
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Hi jbfluor

Try:
Code:
LastRow = Workbooks("Book4").Worksheets("Sheet1").Cells. _
          Find(What:="*", _
          Searchdirection:=xlPrevious, _
          searchorder:=xlByRows).Row

Edit: Not sure, but this may only work if the workbook is open.
It should return the last row containing data.
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

thats what i thought it was but its coming up with a syntax error.

I have tried:

LastRow = [Prelim 19 Sept 2006.xls]"Prelim 19 Sept 2006"!UsedRange.Rows.Count

and

LastRow = [Prelim 19 Sept 2006.xls]Prelim 19 Sept 2006!UsedRange.Rows.Count

and neither works

what you are doing right here is combining a worksheet formula with VBA code. If you want to use it in your VBA, use:

Code:
LastRow = Workbooks("Prelim 19 Sept 2006.xls").Sheets("Prelim 19 Sept 2006").UsedRange.Rows.Count
 

jbfluor

New Member
Joined
Sep 27, 2006
Messages
6
Thanks for both your suggestions

Delmar yours seems to work on th efirst bit of my code but then not on the next bits

Harvey - Yours is more along the line si was looking at but when i compile it it highlights yellow and says "runtime error 9 - Subscript out of range"

I cant understand why, any ideas?
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

It usually means either the workbook or the worksheet does not exist (ie. the name is typed wrong)
 

jbfluor

New Member
Joined
Sep 27, 2006
Messages
6
I just changed them all and tried it again and no joy. I have no idea whats going on but its annoying me and im sure it will be something so simple aswell.

Would it help if i put the whole path in for the work book and put active sheet for the sheet as theres only one sheet in the workbook in question??? and what would the code be?

Workbooks (H:\250_Pipe\253 - Material Control\Mat'l Tracking\James MTO Report\Weeks1-2). Activeworksheet. Cells (CurrentPos, "A")
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
it is also possible to use workbooks(1) and workbooks(2), but you have to be sure that you are using the right workbook.

something that can also help is printing the workbook name and then copying it between the parentheses:
debug.print activeworkbook.name
say it says: myfile.xls
then you type:
workbooks("myfile.xls").activate
and execute to see what happens. If no error, put it into the rest of the code and it should work.
 

jbfluor

New Member
Joined
Sep 27, 2006
Messages
6
there does appear to be a problem with the file names but i cant understand it i have checke dthem even renamed them to w1 and w2 and it still wont have it? Have no idea whats wrong now. Thanks for your help though
 

Forum statistics

Threads
1,136,926
Messages
5,678,606
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top