CODEWITHSUNDEEP
vbaexcel
Sunil bhagavath
Sunil bhagavath

Reputation: 39

Selecting a sheet from the value in a cell

Is it possible to select a sheet based on the value in the cell. For example

  Col_1      Col_2      Col_3

  gor         100        jan
  mack        60         jan
  john        40         mar---->this data
  ..... So on

Here am checking for persons who have below 50 hours and if they have 50 hours then i have to go to that sheet and mark them to represent them. here john has below 50 hours then i have to get the sheet name from the col_3 that's here March. I have to goto that mar sheet and mark them. My question is that. Is it possible to select or get a sheet name based on the value in a cell.

Upvotes: 0

Views: 74

Answers (3)

Ryan J
Ryan J

Reputation: 8323

If your cell value is a date string, you can use Format to put it in the string format you require. For example, if your cell contains "3/15/15", you can turn this into the sheet name "Mar-15" using the code below:

Dim dateVal As String
' assume this is your active sheet
Worksheets(1).Activate

' for simplicity, just set a date
Cells(1, 1).Value = "3/15/15"

' read the date from the cell and format to get month name
dateVal = Format(Cells(1, 1).Value, "mmm-YY")
MsgBox ("Month from Date: " & dateVal)

Output shows:

enter image description here

Note that you may need to adjust for the format of your date, as I'm working with a US-English format, but the concept is to format what the value in the cell contains into what you want.

Upvotes: 1

amg
amg

Reputation: 141

Try use the below code. It is written for activecell only but can be extended for range of cell. 

Dim sname As String

If ActiveCell.Value < 50 Then
    sname = ActiveCell.Offset(0, 1).Value & "-" & "15"
    Sheets(sname).Select

End If

Upvotes: 0

Swapnil Wankhede
Swapnil Wankhede

Reputation: 44

About this line ws = Worksheets(Worksheets("Report").Range("H" & intRow1).Value).Activate –

When you want to select or activate any value from particular sheet then those sheet must be activate o/w it gives an error.

Upvotes: 0

Related Questions