Save As Macro that does...

[Bonse]

Hey guys, I'm new to the forum after searching around the internet MrExcel has repeatedly come up so i thought i'd just join and ask myself as i think my request is very specific.

A bit about me as i'm new; i'm 23 year old design engineer from the midlands with only a few passions in life, the misus ofc!, online pc gaming and cars (mainly bmw's) lol

I'm very basic when it comes to excel, in fact i can go as far as to say i don't know anything about the macro system in excel.

How can i make a macro button that on click saves as in the following manor;

save as first 6 characters of a cell (e.g. A1) with the addition to " - Word" on the end in a certain directory, e.g. "L:\Works\100000-199999"

So on press, if cell A1 was "123456789" it would save: "123456 - Word" in "L:\Works\100000-199999"

Is this too difficult? Sorry to be cheeky.

Thanks all,

Tom

 

[moonfish]

Could you specify your version of Excel? 2007 and 2010 have different file formats so it's relevant when trying to save your document.

 

[VoG]

Try like this with a button from the Controls toolbar

Private Sub CommandButton1_Click()
ActiveWorkbook.SaveAs Filename:="L:\Works\100000-199999\" & Left(Sheets("Sheet1").Range("A1").Value, 6) & " - Word.xls"
End Sub

 

[Bonse]

Could you specify your version of Excel? 2007 and 2010 have different file formats so it's relevant when trying to save your document.

My apologies.

It's Excel 2007.

 

[moonfish]

The macro I wrote saved as a macro-free workbook before I copied its contents to this forum.

Fortunately, VoG wrote something less paradoxal :P

 

[VoG]

For 2007

Private Sub CommandButton1_Click()
ActiveWorkbook.SaveAs Filename:="L:\Works\100000-199999\" & Left(Sheets("Sheet1").Range("A1").Value, 6) & " - Word.xlsx", FileFormat:=51
End Sub

 

[Bonse]

Sorry if i'm so thick it's insulting - but where do i put this? lol i've tried in the macro thing but it said compile error

 

[VoG]

Click the Office button (top left), click Excel Options, tick Show developer tab in the ribbon and click OK.

On the Developer tab, from the Insert menu select an ActiveX button and draw it out on the sheet.

Right click the button, select View Code and paste in the code. ALT + Q to close the code window.

On the Insert menu click Exit design mode.

 

[Bonse]

Thanks, you're a star!

However i'm getting a compile error.

It's highlighting "ActiveWorkbook" and saying "Expected: end of statement"

Cheers

 

[moonfish]

It works when I try it. Try this one, then?

Private Sub CommandButton1_Click()
    ActiveWorkbook.SaveAs Filename:="C:\" & Left(Sheets("Sheet1").Range("A1").Value, 6) & " - Word.xls", FileFormat:=52
End Sub