Help With code to name file PLEASE

[Mister H]

Hi:

I am trying to alter a code so it names the file the way the user requires it. I can not seem to get it right. I want the code to take the cvalue in C2 of the active sheet and trim the last 10 characters off it. How do i do that? I got it to do it in a cell but unfortunately I can not get it to work in VBA. No error it just does not trim off the 10 characters. Here is the formula:
=LEFT(C2, LEN(C2) - 10)

Here is the line of code I tried to put it in:

TempFileName = FilePath & Format(ActiveSheet.Range("C1"), "yyyy mm dd") & " " & "MOHLTCK" & " " [COLOR=red][B]& Format(ActiveSheet.Range("C2"), "=Left(C2, Len(C2) - 10)")[/B][/COLOR] & " " & "REV"

Any assistance as ALWAYS is GREATLY Appreciated

THANKS,
Mark

 

[VoG]

Hi Mark. You have an unnecesserary =

TempFileName = FilePath & Format(ActiveSheet.Range("C1"), "yyyy mm dd") & " " & "MOHLTCK" & " " & Format(ActiveSheet.Range("C2"), "Left(C2, Len(C2) - 10)") & " " & "REV"

 

[Joe4]

Try:

... & LEFT(Range("C2"),LEN(Range("C2"))-10) & ...

 

[Mister H]

THANKS to both of you for your replies.

Peter: I had tried removing the = sign but it still would not trim it. Maybe it has something to do with the fact that itC2 is a merged cell.
I actually just noticed that or i would have mentioned it before.

Joe4: Your suggestions seems to be doing the trick.

THANKS agin to BOTH of you for your expertise.

Have a GREAT day,
Mark

 

[VoG]

Merged cells are the work of the

If you are going to use VBA, Center Across Selection is preferable.

 

[Mister H]

THANKS Peter... I have been learning that over the years. I try not to use them when I can get by without it. Unfortunately I am not able to alter the spreadsheet as it belongs to someone else. Is Excel 2010 still confused by Merged Cells?

THANKS,
Mark

 

[VoG]

In Excel 2010 it is still Excel programmers that have problems with merged cells.

 

[Mister H]

THANKS for the Head Up