Clicky

I have two open Excel  workbooks.  In Workbook no 1  i  want to have a function in Visual Basic code that  read data in a specific cell in the other open workbook ( Horse.xls).
I have tried the following;
Range("A1") =  Worksheets("Horse.xls").Sheets("Bet Angel"),Range("B7")

But it does not work.

Any good,  simple ideas  ?

Regards
Harald

asked 12/11/2011 11:23

hwibye's gravatar image

hwibye ♦♦


22 Answers:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
dim ar as workbook, at as workbook
dim wsSheet as worksheet, nSheet as worksheet

Set ar = Workbooks("Horse.xls")
Set at = Workbooks("destinationworkbookname.xls")

Set wsSheet = Sheets("originatingworksheetname")
Set nSheet = Sheets("destinationworksheetname")

With wsSheet
            .Range("A1:J1").Copy nSheet.Range("A1:J1")
End With


modify as you wish
link

answered

Shanan212's gravatar image

Shanan212

you need workbooks object to refer to the other sheet.
you had a comma in there too where there should have been a fullstop (or period)
1:
Range("A5").Value = Workbooks("Horse.xls").Sheets("Bet Angel").Range("A1").Value
link

answered 2011-12-12 at 07:33:02

EoDawg's gravatar image

EoDawg

hi

I HAVE TRIED YOUR SOLUTION AS I LIKED ITS SIMPLICITY BUT UNFORTUNATELY i GOT THE FOLLOWING ERROR:

RUN TIME ERROR 9
SUBSCRIPT OUT OF RANGE

REGARDS
HARALD
link

answered 2011-12-12 at 07:34:03

hwibye's gravatar image

hwibye

make sure the names are correct, if there's a spelling mistake it won't be able to find the sheet.

Horse.xls may need to be open also, although I'll have to test this to be sure.

so, check spellings, if everything looks ok, have horse.xls open before you run it.

if it turns out horse.xls needs to be open we can add code before that statement to open the horse.xls file.
link

answered 2011-12-12 at 08:38:49

EoDawg's gravatar image

EoDawg

I hav e tried sulution from SHANAN212 but I get the following error
Error no 9
Subscridt out of range
link

answered 2011-12-12 at 08:46:01

hwibye's gravatar image

hwibye

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
Sub sampler()


Dim ar As Workbook, at As Workbook
Dim wsSheet As Worksheet, nSheet As Worksheet


Set ar = Workbooks("Horse.xlsx")

Set wsSheet = Sheets("sample")
Set nSheet = ar.Sheets("sheet1")

With wsSheet
.Range("A1:J1").Copy nSheet.Range("A1:J1")
End With
End Sub


Try the above but dont forget to change the sheet and ranges
link

answered 2011-12-12 at 08:52:58

Shanan212's gravatar image

Shanan212

wsSheet is the origin sheet
Sheet1 is the destination sheet (so change those)

also change the ranges
link

answered 2011-12-12 at 08:56:52

Shanan212's gravatar image

Shanan212

Just retried mine with the horse files closed, and it fails.
with the horse file open it works.

but whatever solution is best for you. If Shanan's proves better then no sweat.
link

answered 2011-12-12 at 08:57:32

EoDawg's gravatar image

EoDawg

Shanan212   and  Fodewq
Sorry
I do not quite get the idea as i am not too familiar with VB.
.
Origin Workbook is:   Horse-B   and Sheet name is Bet Angel  

Destination  Workbook is: Horse-P   and Sheet name also  Bet Angel.

I want to transfer the data from Horse-B, Bet Angel, cell AV6  to Cell L7 in  Horse-P bet Angel sheet.

Both workbokks are open and have only one  Sheet (Bet Angel)

Destination Workbook is  Horse-P  and Sheet name is also Bet Angel

How will it look then ?
link

answered 2011-12-12 at 09:15:12

hwibye's gravatar image

hwibye

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
Sub sampler()
Dim ar As Workbook, at As Workbook
Dim wsSheet As Worksheet, nSheet As Worksheet

Set ar = Workbooks("Horse-P.xlsx")
Set wssheet = ActiveWorkbook.ActiveSheet
Set nSheet = ar.Sheets("Bet Angel")

With wsSheet
.Range("AV6").Copy nSheet.Range("L7")
End With

End Sub
link

answered 2011-12-12 at 09:17:54

Shanan212's gravatar image

Shanan212

I prefer this solution if i can get it to work.
Will try again-harald


Range("A5").Value = Workbooks("Horse.xls").Sheets("Bet Angel").Range("A1").Value
link

answered 2011-12-12 at 09:30:54

hwibye's gravatar image

hwibye

I think EoDawg's solution is giving error because of the xlsx

Correcting his code would be

1:
2:
Range("A5").Value = Workbooks("Horse-P.xlsx").Sheets("Bet Angel").Range("A1").Value
link

answered 2011-12-12 at 09:31:24

Shanan212's gravatar image

Shanan212

Range("AV6").Value = Workbooks("Horse-P.xlsx").Sheets("Bet Angel").Range("L7").Value

^ that
link

answered 2011-12-12 at 09:39:37

Shanan212's gravatar image

Shanan212

The original post has "horse.xls" but regardless, you seem to be making good progress with a full vb sub there.
link

answered 2011-12-12 at 09:40:09

EoDawg's gravatar image

EoDawg

EoDavo:

I have tried your  solution in 2  clean workbooks but same error.
I must do something wrong
 Below is my  code. in Workbook Test trying to read data in Horse/  Bet Angel / A1


Private Sub Worksheet_Calculate()

Range("A5").Value = Workbooks("Horse.xls").Sheets("Bet Angel").Range("A1").Value

End Sub
link

answered 2011-12-12 at 09:42:30

hwibye's gravatar image

hwibye

what version of excel are you using? I've tested both codes and both gave the error as you mentioned.

Upon changing the extension as "xlsx" they worked (specially since I am using 2010) but if you are using 2007, you have to change that as well (unless the file is saved as 2003 format)
link

answered 2011-12-12 at 09:45:46

Shanan212's gravatar image

Shanan212


Good question

I am using Excel 97- 2003

Harald
link

answered 2011-12-12 at 09:54:20

hwibye's gravatar image

hwibye

I think everyone here is giving basically the right answer, it's just the name of the source woorkbook that seems to be at issue.  @hwibye says it is Horse-B, and as he is Excel 2003, it will be Horse-B.xls.

so
1:
2:
3:
4:
5:
[Private Sub Worksheet_Calculate()

Range("A5").Value = Workbooks("Horse-B.xls").Sheets("Bet Angel").Range("A1").Value

End Sub

should work PROVIDING a workbook called Horse-B.xls is open AND it has a sheet called "Bet Angel" (case is not important on either).  The subscript out of range error is because one of those things is not true.

My other observation on this is that I don't recommend you do it in the Worksheet_Calculate sub, as this will get run every time the worksheet calculates, which could be very frequently indeed, and the value probably doesn't change that often.  Would it be better say in the Workbook_Open routine?
link

answered 2011-12-12 at 11:26:51

andrewssd3's gravatar image

andrewssd3


It might be my old version of Excel  MS 2000 that  gives the problems.  Still does not work.

I even tried with a Workbook named H  and a Worksheet named B  which was open.


Range("A5").Value = Workbooks("H.xls").Sheets("B").Range("A1").Value

Stil  got error 9
Subascriot out of range.
link

answered 2011-12-13 at 05:59:50

hwibye's gravatar image

hwibye

Try running this code with your workbooks open and post the output that gets printed in the Immediate window in the VBA editor (if you don't have the immediate window displayed press Ctrl-G to bring it up.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
Sub Macro1()
'
    Dim w As Excel.Workbook
    Dim s As Excel.Worksheet
    
    For Each w In Application.Workbooks
        Debug.Print ">" & w.Name & "<"
        For Each s In w.Worksheets
            Debug.Print ">>" & s.Name & "<"
        Next s
   Next w

End Sub
link

answered 2011-12-13 at 11:13:12

andrewssd3's gravatar image

andrewssd3


Here is the "simple" and effective solution that worked and I liked:

Worksheet_Activate()
    ThisWorkbook.ActiveSheet.Range("M7") = "='C:\users\hwibye\My Documents\[HorseB.xls]Bet Ael'!L7"
End Sub

Thanks for all your effort.
Regards
Harald
link

answered 2011-12-13 at 11:27:07

hwibye's gravatar image

hwibye

Problems  with my  Excel 2000.

link

answered 2011-12-13 at 11:53:09

hwibye's gravatar image

hwibye

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1

Asked: 12/11/2011 11:23

Seen: 265 times

Last updated: 12/17/2011 05:20