Have Excel VB Read from two sheets
 

Goodmorning!

I don't know much about VB, but I have an excel workbook that has VB.
I have updated it in the past to read current info, but have now run
out of space on the first worksheet and will need to start adding info
to a second worksheet. I need to know how to get excel to read the
second worksheet, when a particular Schedule is seleted. All of the
info it currently reads is on a sheet called, "PPO". I am now going to
have a second sheet and need it to read from there when it gets to say,
Sch #704

I'm attaching part of the VB code.

Any help would be greatly appreciated.


Thanks,



Dim Schedule_No As String
Dim ADA_No As String


Sub calculate()
Let Schedule_No = Sheets("PPO").[B5]
Let ADA_No = Sheets("PPO").[B8]

Range("A1").Select
ActiveCell.Offset(0, 10).Activate

Call calculate_ADA(ADA_No)
Sheets("PPO").[F2].Value = ActiveCell.Value

Call calculate_Schedule(Schedule_No)
Sheets("PPO").[F1].Value = ActiveCell.Value

SendKeys "{Tab}"
Range("A5").Select


End Sub
Sub calculate_ADA(ADA_No)

Select Case ADA_No
Case "D0120", "d0120"
ActiveCell.Offset(1, 0).Activate
Case "D0140", "d0140"
ActiveCell.Offset(2, 0).Activate
Case "D0150", "d0150"
ActiveCell.Offset(3, 0).Activate
Case "D0160", "d0160"
ActiveCell.Offset(4, 0).Activate
Case Else
Range("B8").Activate
End Select
End Sub
Sub calculate_Schedule(Schedule_No)

Select Case Schedule_No
Case 700
ActiveCell.Offset(0, 1).Activate
Case 701
ActiveCell.Offset(0, 2).Activate
Case 702
ActiveCell.Offset(0, 3).Activate
Case 703
ActiveCell.Offset(0, 4).Activate
Case Else
Range("B5").Activate
End Select
End Sub


--
cowannbell
------------------------------------------------------------------------
cowannbell's Profile: http://www.excelforum.com/member.php...o&userid=24522
View this thread: http://www.excelforum.com/showthread...hreadid=381184

Have Excel VB Read from two sheets
 

Case 704
Worksheets("Sheet2").Activate
Range("B5").Select
Case Else


However, it is better to refer to the sheet or a range rather than activate
it. Such a change might require you to reengineer all your code.

--
Regards,
Tom Ogilvy


"cowannbell" wrote
in message ...

Goodmorning!

I don't know much about VB, but I have an excel workbook that has VB.
I have updated it in the past to read current info, but have now run
out of space on the first worksheet and will need to start adding info
to a second worksheet. I need to know how to get excel to read the
second worksheet, when a particular Schedule is seleted. All of the
info it currently reads is on a sheet called, "PPO". I am now going to
have a second sheet and need it to read from there when it gets to say,
Sch #704

I'm attaching part of the VB code.

Any help would be greatly appreciated.


Thanks,



Dim Schedule_No As String
Dim ADA_No As String


Sub calculate()
Let Schedule_No = Sheets("PPO").[B5]
Let ADA_No = Sheets("PPO").[B8]

Range("A1").Select
ActiveCell.Offset(0, 10).Activate

Call calculate_ADA(ADA_No)
Sheets("PPO").[F2].Value = ActiveCell.Value

Call calculate_Schedule(Schedule_No)
Sheets("PPO").[F1].Value = ActiveCell.Value

SendKeys "{Tab}"
Range("A5").Select


End Sub
Sub calculate_ADA(ADA_No)

Select Case ADA_No
Case "D0120", "d0120"
ActiveCell.Offset(1, 0).Activate
Case "D0140", "d0140"
ActiveCell.Offset(2, 0).Activate
Case "D0150", "d0150"
ActiveCell.Offset(3, 0).Activate
Case "D0160", "d0160"
ActiveCell.Offset(4, 0).Activate
Case Else
Range("B8").Activate
End Select
End Sub
Sub calculate_Schedule(Schedule_No)

Select Case Schedule_No
Case 700
ActiveCell.Offset(0, 1).Activate
Case 701
ActiveCell.Offset(0, 2).Activate
Case 702
ActiveCell.Offset(0, 3).Activate
Case 703
ActiveCell.Offset(0, 4).Activate
Case Else
Range("B5").Activate
End Select
End Sub


--
cowannbell
------------------------------------------------------------------------
cowannbell's Profile:
http://www.excelforum.com/member.php...o&userid=24522
View this thread: http://www.excelforum.com/showthread...hreadid=381184

Have Excel VB Read from two sheets
 

Okay, I'm sure I didn't do this correctly, but this is what I input and
I get the below error.

ActiveCell.Offset(0, 238).Activate
Case "7CB"
Worksheets("PPO-Custom").Activate
Range("B5").Select
Case Else

End Select
End Sub


Error: Can't execute code in break mode


--
cowannbell
------------------------------------------------------------------------
cowannbell's Profile: http://www.excelforum.com/member.php...o&userid=24522
View this thread: http://www.excelforum.com/showthread...hreadid=381184

Have Excel VB Read from two sheets
 

Okay ignore my prior note. I had to close the VB editior and it ran
However, it is still not correct, I've missed something. When it runs
it pulls up the sheet that is referenced and the cursor is in B5. It i
suppose to stay on the same sheet and pull back a fee for tha
particular fee sch and fee code that was selected

--
cowannbel
-----------------------------------------------------------------------
cowannbell's Profile: http://www.excelforum.com/member.php...fo&userid=2452
View this thread: http://www.excelforum.com/showthread.php?threadid=38118

Have Excel VB Read from two sheets
 

ActiveCell.Offset(0, 238).Activate
Case "7CB"
feecode = Worksheets("PPO-Custom").Range("B5").Value
Case Else

End Select

--
Regards,
Tom Ogilvy

"cowannbell" wrote
in message ...

Okay ignore my prior note. I had to close the VB editior and it ran.
However, it is still not correct, I've missed something. When it runs,
it pulls up the sheet that is referenced and the cursor is in B5. It is
suppose to stay on the same sheet and pull back a fee for that
particular fee sch and fee code that was selected.


--
cowannbell
------------------------------------------------------------------------
cowannbell's Profile:
http://www.excelforum.com/member.php...o&userid=24522
View this thread: http://www.excelforum.com/showthread...hreadid=381184