Solved: Save as & file name\path problem

[Phelony]

Hi guys!

As usual, I've been playing around with this teeny bit of code for a while now and have hit a wall.

I fix one part, just to have another complain....and I know it's not even a complex bit of code!!

At present it's complaining that the save as method has failed, although this worked until a few moments ago.

Could I ask one of you gentlemen to have a look and see where todays screw up has arrived from?

In short, this is supposed to:
1. select the appropriate sheet
2. copy it to it's own workbook
3. save it to a specific file path & a file name designated by cell C4
4. print itself out
5. open an e-mail dialog box and attach the file
6. politely close the copy spreadsheet before returning you to the original workbook.

Simple enough...or so I thought...

Any guidance, wisdom or otherwise support would be greatly welcomed

Phel x

PS - This is an old workbook that someone else built and I'm trying to make fit a new process. I know that it's a really awful way of doing this, but it's what I've got to work with!!

[VBA]
Private Sub Command Button 1_Click()
Dim fname As String

Sheets("4series - fees").Select
Sheets("4series - fees").Copy
fname = "C:\Services\People" & "\" & Range("C4").Value
ActiveWorkbook.SaveAs Filename:=fname
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.PrintOut
ActiveWorkbook.Close
Windows("automated forms 2008.xls").Activate
Sheets("INPUT SHEET").Select

End sub
[/VBA]

[Bob Phillips]

What is in C4?

[Phelony]

A client name, simply text. Is that where the problem is?

[Bob Phillips]

I don't know, it may have invalid characters or some control character. Just a thought.

[Phelony]

I've altered the code slightly to see what was coming through as fname and am just getting "C:\services\clients\" so it's not picking up the range command. There is a formula there, but it's being told to copy the value...ich bin confused!

[vba]Dim fname As String
Sheets("4series - fees").Select
Sheets("4series - fees").Copy
fname = "C:\Services\People" & "\" & Range("C4").Value
MsgBox (fname)


'ActiveWorkbook.SaveAs Filename:=fname
'Application.Dialogs(xlDialogSendMail).Show
'ActiveWorkbook.PrintOut
'ActiveWorkbook.Close
'Windows("automated forms 2008.xls").Activate
'Sheets("INPUT SHEET").Select[/vba]

[Bob Phillips]

Qualify the range with the sheet name, you are probably picking up C4 from the wrong sheet.

[Phelony]

Strangely seemed to work as soon as I made it a module rather than a worksheet connected macro...it's predecessor was set up on the sheet rather than a stand alone module, so I simply cannibalised it.

I've put in several sheet("X").select 's to ensure that the right sheet is being used. But it works as intended now.

Glad to know that it wasn't a complete failing of code on my part! Just a failing of logic!

Thank you for your help, got me thinking on the right track!!

Phel x