FORMAT OF A VARIABLE USED IN VBA MACRO PROBLEM

The value in C1 below is a date, e.g. 12/1/06. The date changes monthly. The cell C1 is formatted as Dec - 06 whcih is what I want. However, I need the Dec - 06 to be used in the name of the file noted here as "date1". The problem - date1 needs to be in the "Dec - 06" format but is in "12/1/06" format. How do I get the value of date1 to be exactly what I see in the cell of the spreadsheet

Dim strDate1 As String
date1 = Range("C1").Select
date1 = ActiveCell

Range("A1:E60").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

Dim strRoot As String
strRoot = "U:\Excel\MONTHLY REPORTS\" & date1 & " my name"



Answer this question

FORMAT OF A VARIABLE USED IN VBA MACRO PROBLEM

  • Puffarbubbole

    Change your last line to this:

    strRoot = "U:\Excel\MONTHLY REPORTS\" & Format(date1, "mmm-yy") & " my name"



  • soconne

    Thank you! It works great! I appreciate your help and your expertise! It was probably pretty obvious to you, but I'm learning as I go here, so it was a struggle. Thanks again!
  • Marek Istvanek

    Not to worry, we all started somewhere! Glad it helped.

  • FORMAT OF A VARIABLE USED IN VBA MACRO PROBLEM