Changing cell values based on a worksheet name

  • Thread starter Thread starter jrp7286
  • Start date Start date
J

jrp7286

I am trying to return a value to a cell base on the name of the
worksheet tab. I have around 300 proucts that I need to have a
separate tab for each product. I have used visual basic to name the
tab, now I need to have that same worksheet name appear in a cell.
 
You can do this with or without VBA.

With VBA

dim aWS as worksheet
for each aWS in activeworkbook.worksheets
aws.cells(row,column).value = aws.name 'You fill in the row and column
value
next aws

Without VBA

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1),1))
 
try this Put this into the this workbook mod And A1 will be named the same as
sheet name. Im asuming all sheets will have the same cell of Sheet Name
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Range("A1") = ActiveSheet.Name Then

Exit Sub
Else
Range("A1").Value = ActiveSheet.Name
End If
End Sub
 
Back
Top