Create a macro

  Pedro Campos 23:36 17 May 05

I want to create a macro that when run it will save the current file with a new name an a different location... Is this possible? Can someone help me?

I'm running Windows XP Home and the file is a Microsoft Excel 2003.

  VoG II 08:23 18 May 05

Sub test()


End Sub

  Pedro Campos 12:57 18 May 05

Do you think that its possible that I can programme the macro to change the name of the file automatically so that I dont have to type a new file name on the save as box, but at the same time not replacing the previous saved file?

  VoG II 21:38 20 May 05

Yes, assuming that you can generate the filename in your code:

Sub test2()

Dim fname As String

fname = "my new filename"

ThisWorkbook.SaveAs fname

End Sub

  bretsky 22:02 20 May 05


  Pedro Campos 11:21 21 May 05

It did work, thanks.
But what i realywant to do is save the file with a new name everytime i run that macro... eg: everytime i run the macro it should save the file as "Pedro Week 1", the folowing week i run the macro again but it needs to be saved as "Pedro Week 2" so it does not replaces or asks me if i want to replace the first one and so on.

  Pedro Campos 11:23 21 May 05

What do you mean?

  VoG II 11:57 21 May 05

Save your file as "Pedro Week 1.xls" (without the quotes).

Sub test3()

Dim fname As String, cno As Variant

Dim ix1 As Integer, ix2 As Integer

fname = ThisWorkbook.Name

ix1 = InStr(fname, "Week ") + 4

ix2 = InStr(fname, ".") - 1

cno = Val(Mid(fname, ix1 + 1, ix2 - ix1)) + 1

fname = Left(fname, ix1) & cno & ".xls"

ThisWorkbook.SaveAs fname

MsgBox prompt:=fname, Title:="File Saved As", Buttons:=vbInformation

End Sub

  VoG II 12:10 21 May 05

Sorry, to ensure that the new file foes into the same folder as the old one, change the SaveAs line to:

ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & fname

  dogbreath1 12:48 21 May 05

I have a spreadsheet with a generic filename which resides in My Documents. How could I force the sheet, when modified, to autosave with a new filename (generated from a cell entry within the spreadsheet) to a new folder, say, My Modified Sheets. I.e. All modified sheets (renamed according to an entry made on the sheet in a specific cell) being saved in one folder but a different folder from the folder where the generic sheet resides. Hope that's clear and thanks in anticipation.

  VoG II 13:14 21 May 05

With filename (e.g. dogbreath1) in A1 and folder name (e.g. c:\my documents\differentfolder) in A2

Sub test4()

Dim tmp

On Error Resume Next

tmp = Dir(Sheet1.Range("A2").Value & "\*.*", vbDirectory)

If Err = 0 Or tmp = "" Then

MkDir Sheet1.Range("A2").Value

End If

On Error GoTo 0

ThisWorkbook.SaveAs Sheet1.Range("A2").Value & "\" & Sheet1.Range("A1").Value & ".xls"

End Sub

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Animator Emanuele Kabu’s psychedelic video is a stunning tribute to Lisbon city

Best Mac antivirus 2017