Read data from cells in different worksheets

E

emma

Hi!
I have a problem with my VBA code when I try to read values from cells
in two different worksheets. When I run the macro the program tells me
that I´m out of bound. But everything works when I save the data in
the same worksheet. How shold I write the code so that this simple
thing could work?
The data should be read when the user makes a buttonlick and then it
will be used later on.

The code is simple like this:

Public Sub Button1_Click()
A = Worksheets("Page1").Range("N8").Value
B = Worksheets("Page2").Range("I8").Value

I want to save the values from cell N8 in the variable A and so on...
It seems like only one worksheet can be active when I write the code
like this. I have tried commands like Sheets("Page2").Select
but still it is not working

I hope you can help me!
 
O

Otto Moehrbach

Exactly what does the error message say? When you click on "Debug" in the
error message dialog box, what line of code is highlighted? Reduce your
macro (or write another one) to just the 2 lines you show in your post.
Does the error still occur? Please post back with this info. HTH Otto
Hi!
I have a problem with my VBA code when I try to read values from cells
in two different worksheets. When I run the macro the program tells me
that I´m out of bound. But everything works when I save the data in
the same worksheet. How shold I write the code so that this simple
thing could work?
The data should be read when the user makes a buttonlick and then it
will be used later on.

The code is simple like this:

Public Sub Button1_Click()
A = Worksheets("Page1").Range("N8").Value
B = Worksheets("Page2").Range("I8").Value

I want to save the values from cell N8 in the variable A and so on...
It seems like only one worksheet can be active when I write the code
like this. I have tried commands like Sheets("Page2").Select
but still it is not working

I hope you can help me!
 
E

emma

When I debug my code it highlights the line
B = Worksheets("Page2").Range("I8").Value
and tells me that the index is out of bound. It seems like the second
worksheet in my workbook is not reachable. I have tried to put this two
lines separate in a new macro but I get the same error. Maybe it´s
because the button I have created in excel is on another worksheet?
 
O

Otto Moehrbach

No, the button being on another sheet is not the problem because you
adequately reference the other sheet. Do this.
Write a new macro like this:
Sub Test()
MsgBox "Page1 is: " & Len(Sheets("Page1").Name)
MsgBox "Page2 is: " & Len(Sheets("Page2").Name)
End Sub
The 2 message boxes will tell you the number of characters in the names of
the 2 sheets as they are written in the sheet tabs.
Now look at the same sheet names in your macro code and see if those names
have the same number of characters as in the sheet tabs. Sometimes a space
here and there is overlooked and causes this problem.
If that's not the problem, do this:
Open a new blank file. In that new file name two of the sheets the same as
in the problem file. Write a macro within that new file with just those two
lines that you posted in your original posting. Make sure that you get the
same error as before. If you do, send me that file via email. My email
address is [email protected]. Remove the "nop" from this address. HTH
Otto
When I debug my code it highlights the line
B = Worksheets("Page2").Range("I8").Value
and tells me that the index is out of bound. It seems like the second
worksheet in my workbook is not reachable. I have tried to put this two
lines separate in a new macro but I get the same error. Maybe it´s
because the button I have created in excel is on another worksheet?
 
E

emma

I found the error and the code is now working!
Thank you for your help
I appreciate it!
/Emma
 
O

Otto Moehrbach

Please post what you found. It would help the many people who look at these
newsgroups. Otto
 
Top