CODEWITHSUNDEEP
excelvba
user2103670
user2103670

Reputation: 1721

Get a value from the sheet name and cell address

Many times I need to get a value from multiple sheets (to some crazy workbooks - it can be up to 200 sheets). The problem is all these 200 sheets have the same structure even their name and it is crazy for me go to link one by one though these 200 sheets. Is there any way to create a user-defined function, something like

=getValue(sheetName,cell address)

I tried

Function GetValue(sheetName As String, cellAddress As String) As Variant
   GetSheetValue = Range(sheetName & "!" & cellAddress).Value
End Function

which works well until I switch between Excel files. The function starts to return #Value which my feeling is that it tries to search for SheetA,B,C,D @A1 on other open workbooks.

enter image description here

Upvotes: 1

Views: 16156

Answers (2)

Santosh
Santosh

Reputation: 12353

Try below code

Function GetValue(sheetName As String, cellAddress As String) As Variant
   GetSheetValue = ThisWorkbook.Sheets(sheetName).Range(cellAddress)
End Function

Upvotes: 2

Aaron Thomas
Aaron Thomas

Reputation: 5281

This can be solved in VBA fairly easily. A For...Next loop would go through all 200, worksheets, returning the values. For example:

my_index = 1
For Each ws In Worksheets
  If ws.Name <> worksheet_to_store_values_on Then
    worksheet_to_store_values_on.Cells(my_index, 1).Value = _
    ws.Range(myrange).Value
    my_index = my_index + 1
  End If
Next

... this will loop through all worksheets in your active workbook, find the value in myrange on each sheet, and stores this value in the worksheet "worksheet_to_store_values_on" in row "my_index" of column A. You can adapt to your particular situation.

Upvotes: 1

Related Questions