VBA command for "save as..."

[merlin_the_magician]

I have a template workbook that, when filled out, i want to save under a specific name.
Therefore, a value that should be part of the filename, can be entered in cell C1.

Question is... what VBA command to use, in order tp create a "save as" command, which includes the value in cell C1?

filename should look like this: "[C1 value]specific filename.xls"

 

[Philem]

Hullo. Something along these lines:

Worksheet.SaveAs= varFileName & "specific filename.xls"

UNTESTED PSEUDOCODE

HTH

P

 

[Al Chara]

Try the following code:

<font face=Courier New>Sub SaveAs()

<SPAN style="color:darkblue">Dim</SPAN> ShName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>

ShName = "Sheet1"

    ActiveWorkbook.SaveAs Filename:=Worksheets(ShName).[c1] & "specific filename.xls"
        
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[merlin_the_magician]

neighter one of them seem to work....

This is the VBA code alrady in the workbook. How to apply the "save as" thing?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim myRange As Range

Set myRange = Range("D15, D16, D25, D26, G41, G42, H15, H16, H17, H18, H19, H23, H24, H25, H26, I12, I13, J20, J28, J29")
Set isect = Application.Intersect(myRange, Target)
If Not isect Is Nothing Then
MsgBox "Deze cel bevat een formule. U heeft geen rechten deze cel te bewerken."
On Error Resume Next
If Target.Count = 1 Then Target.Cells.Offset(0, 0).Select
If Err Or Target.Count > 1 Then Cells(0, 0).Select
On Error GoTo 0
End If


Set myRange = Range("N43, N44")
Set isect = Application.Intersect(myRange, Target)
If Not isect Is Nothing Then
MsgBox "© 2003 JBvD / HHSAV Versie 2.8"
On Error Resume Next
If Target.Count = 1 Then Target.Cells.Offset(0, 0).Select
If Err Or Target.Count > 1 Then Cells(0, 0).Select
On Error GoTo 0
End If

With Application
.CutCopyMode = False
.CellDragAndDrop = False
End With

End Sub

 

[mweaver]

Merlin,

The code that Philem and Al Chara are recommending should reside in a module in your vba project.

In the past my search of this board has resulted in multiple vba code for custom FileSaveAS that alowed for sheetname, filename, date, time, cell and custon text. There is a wealth of information on this topic using the search.

If your search results ar not fruitful please post back. someone will help.

Good Luck

mweaver

 

[merlin_the_magician]

No way i can get it to work... no matter what i try, it contantly runs into error.


Let me be more specific: i want the file to be named "[C1 value] kilometer declaratie" The C1 cell is located on a sheet called "voorblad"

Can someone actually FILL OUT the vba code for me please?

Also, i would like this macro ONLY to work on the "save"or "save as" command

 

[Al Chara]

Try the following code:

<font face=Courier New>Sub procSaveAs()

<SPAN style="color:darkblue">Dim</SPAN> ShName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, FileName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>

ShName = "voorblad"
FileName = " kilometer declaratie"

    ActiveWorkbook.SaveAs FileName:=Worksheets(ShName).[c1].Value & FileName & ".xls"
        
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

I dont't know what you mean by the following:

"Also, i would like this macro ONLY to work on the "save"or "save as" command"

 

[merlin_the_magician]

Thank you AlChara! I already figured out the VBA code myself, but what bothers me, is that the file constanly keeps saving.
Whenever i skip from one cell to another, it the file autosaves.

Therefore, it would be very conveniant, if the file would only save on exit or on the "save" or "save as..." command from menu bar.
Is this possible?

 

[simsy]

I have similar, though not identical, function running on a worksheet. I have it associated with a command button_click() event.

If you do this it should only run when the button is clicked.

It seems that you have it associated with a Change event of some kind?

I hope this helps,

Regards,

Simsy

 

[Al Chara]

Therefore, it would be very conveniant, if the file would only save on exit or on the "save" or "save as..." command from menu bar.
Is this possible?

You can put the code in one of the workbook level events, BeforeClose or BeforeSave.