Go to Sheet named in cell

immyjimmy

Active Member
Joined
May 27, 2002
Messages
254
Hello, all..

I have a user input sheet which asks a user for a machine and a platform and then the number of documents for each part of the machine. Easy enough.

Once the user has populated the form, I need the data copied to the appropriate platform sheet. Cell E2 contains the platform name. I know how to copy and paste the data.

I just need to know how to tell VBA to go to the sheet named in cell E2.

Thanks in advance,
Jim
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hello

Something like this.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br>  <SPAN style="color:#00007F">With</SPAN> Sheet1<br>    sh = .Range("E2")<br>    .Cells(1, 1) = Sheets(sh).Range("A1")<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
I ran the code and the only thing that happened was that row 1 (which had been 13.5 in height) changed to 383.25.

I thought "Well maybe 'Sheet1' needs to be the actual name of the sheet" (I get confused about whether my code should refer to the sheet name or sheet number). After changing "Sheet1" to "InputSht" - which is the name of the sheet being referenced, I got a
Run-time error '424':
Object required
message.

I clicked "Debug" and the "sh = .Range("E2")" line is highlighted.

Any ideas?

Thanks in advance...
Jim
 
Upvote 0
Hello Jim

I just need to know how to tell VBA to go to the sheet named in cell E2

The code that I posted is just to illustrate the above, to try it out, open a new workbook, with at least two worksheets.
In the first sheet named "sheet1", place in cell E2 "Sheet2", without the quotes, or if you rename a sheet place the new name in E2.
Then in Sheet2 A1 place "Hello", put the code in a standard module then run.
Excel Workbook
ABCDE
1
2Sheet2
3
4
Sheet1
Excel 2010
Excel Workbook
A
1Hello
Sheet2
Excel 2010
See the link.

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
 
Upvote 0
Solution
Hi, meldoc...

I did as you suggested. In cell "E2" of Sheet1 , I typed "Sheet2". In cell "A1" of Sheet2, I typed "Hello". I posted your code in a module and ran it. Sheet2 remained the same. Sheet1 now had Sheet2 in cell "E2" and added "Hello" to cell "A1".

What I'm trying to accomplish is this:
If the value of cell "E2" equals "Sheet2", then go to Sheet2.
If the value of cell "E2" equals "Sheet3", then go to Sheet3.
If the value of cell "E2" equals "Sheet4"... and so on.

It seemed to me that rather than have a bunch of If/Then lines, save the value of "E2", then go to the page listed in that value.

Something like:
sh = value("E2")
select.activesheet.(sh)

I know that syntax is wrong, but in pseudo-code, it comes close to my intent. I'm open to whatever will work.

Thanks,
Jim
 
Upvote 0
Meldoc,

Okay, I got it...

Sub Locate_Sheet()
With Sheet1
Sh = .Range("E2")
Sheets(Sh).Select
End With
End Sub

works. Thanks for pointing me in the right direction.
 
Upvote 0
You can also do this in a single like:

Code:
Sheets(Sheets("Sheet1").Range("E2").value).Select

Or, if you have to refer to that sheet often, consider making a variable for it:

Code:
Dim ws as Worksheet
Set ws = Sheets(Sheets("Sheet1").Range("E2").value)
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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
Back
Top