CODEWITHSUNDEEP
vbaexcelreference
Mus
Mus

Reputation: 7530

How to pull the worksheet name from an externally-referenced cell in another worksheet

How to pull the worksheet name from an externally-referenced cell in another worksheet

Cell B1 in my worksheet WS1 shows a value from a referenced cell RefCell in worksheet WS2.

How can I show which worksheet RefCell exists in in cell A1 within WS1?

Something like this is what I need to happen:

 __________________________________
|_______|___A____|___B____|___C____|
|___1___|__Pets__|__Dog___|________|
|___2___|__Fruit_|__Apple_|________|

  *Column B contains referenced values from other worksheets (within the same workbook).
  *The value in Column A detects the worksheet name of the value in Column B.

Can this be achieved in VBA? How would it be implemented?

Upvotes: 0

Views: 56

Answers (2)

user3598756
user3598756

Reputation: 29421

you could use

Sub main()
    Dim cell As Range

    For Each cell In Range("B2", Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeFormulas) '<--| loop through column B cells with formulas found from row 2 down to last not empty one
        cell.Offset(, -1) = Split(Replace(cell.Formula, "=", ""), "!")(0) '<--| extract worksheet name out of current cell formula and write it in corresponding column A cell
    Next
End Sub

Upvotes: 1

Sivaprasath Vadivel
Sivaprasath Vadivel

Reputation: 541

since the reference is stored as a formula in the cell.

ex the cell B2 would have a formula of =pets!refcell

so what you can do is get the formula use instr funcion to get the length of sheet name and use left to get the sheet name and assign it to the cell

B_formaula= str(sheet1.range("B2").formula)
st_name=left(B_formula,instr(1,B_formula,"!",vbbinarycompare)-1)
sheet1.range("A2").value=st_name

i hope this helps.

sub st_name()
dim ws as worksheet
set ws=thisworkbook.sheets(1)
irow=ws.range("B1:B"& rows.count).end(xlup).row
for i = 1 to irow
B_formaula= str(ws.range("B"& i).formula)
st_name=left(B_formula,instr(1,B_formula,"!",vbbinarycompare)-1)
ws.range("A"& i).value=st_name
next i
end sub

this subroutine will check all the values in b column from row no 1 to last row and add the sheet name to the column A.

I havent tested the code yet. try the code if any error revert back.

Upvotes: 0

Related Questions