Select Worksheet and then Reference

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
553
Hi,

I have a button that i want to add code to. When this button is clicked, a box will appear that lets the user select the worksheet they want to use. I then need to put language in similiar to ws='user slectected worksheet' so that i can import data from that worksheet.

Thanks!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,590
Office Version
2019
Platform
Windows
Place this code attached to your button

Code:
Dim sName As String
sName = InputBox("What Sheet to open?")
Sheets(sName).Select
 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
553
OK, this works. I just don't know how to save the selected workbook name in my code. So that ws="selected workbook"

Dim book As Workbook, sheet As Worksheet
For Each book In Workbooks
UserForm1.ComboBox1.AddItem book.Name
Next book
UserForm1.Show
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,023
Office Version
365
Platform
Windows
Maybe something like
Code:
Set Ws = Workbooks(ComboBox1.Value).Sheets("Sheet1")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,114
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top