How can I get the data by entering sheet name?

Asghar Hussain

New Member
Joined
Mar 12, 2021
Messages
10
Office Version
  1. 2007
Platform
  1. Windows
Hi guys,
I have a Excel work book consisting of 200+ sheets (tabs), every sheet has some data in it, all I want is I enter the sheet name and I get the data present in it.
Like, = vlookup(sheet name), and I get the data present in the sheet.
How can I do this guys
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,676
Office Version
  1. 2013
Platform
  1. Windows
In your post you say:
My data in every tab is in row "A to K"

There is no Row "A"
So I assume you mean column A to K
So this script will copy the entire columns A to K

So on sheet named "INDEX" if you enter "Dad" in Range("J2")
The script will copy columns A to K of sheet named "Dad" to Range("L1") of sheet named "INDEX"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/18/2021  4:22:30 PM  EDT
On Error GoTo M
If Target.Address = Range("J2").Address Then
Dim ans As String
ans = Range("J2").Value
Sheets(ans).Range("A:K").Copy Range("L1")
End If
Exit Sub
M:

MsgBox "There is no sheet named " & Target.Value, , "Oops"

End Sub
 
Solution

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Asghar Hussain

New Member
Joined
Mar 12, 2021
Messages
10
Office Version
  1. 2007
Platform
  1. Windows
Bro, Thanks a lot, This is the exact thing I was looking for,
I am completely freaked out right now,
Also I have checked by adding new sheets it works fine,
Once again, Thanks a lot
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,676
Office Version
  1. 2013
Platform
  1. Windows
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
 

Forum statistics

Threads
1,141,413
Messages
5,706,298
Members
421,440
Latest member
cmphares

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
Top