|I mean this in the nicest way and am just trying to teach you without doing it for you.|
If you look at the code you posted, do you see anything that looks like a cell reference? The cell you want to use is C4, so do you see anything in the code that looks similar to C4 or B3 or F6, just as examples?
That's what you need to change so that the code picks up the value in C4.
However, I would like add one more comment. You used the words "which is simply a date".
Dates in Excel are far from "simple" since that are stored internally as a number with the integer portion representing the date and the decimal portion representing the time.
What you see displayed in the cell is based solely on how the cell is formatted.
What you get as a tab name after running the code above may or may not be what you expect. In fact, it may not even work at all.
You may need to supply some more details before we can offer any more assitance. Specifically, what do you have in C4 and what do you want the Tab name to show.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.