How can I get the data by entering sheet name?

[Asghar Hussain]

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]

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

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

 

[Asghar Hussain]

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]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.