Save As in a Macro
 

Function NewName(i As Long) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = "\.[A-z]+$"
NewName = ThisWorkbook.Path & _
Application.PathSeparator & _
RE.Replace(ThisWorkbook.Name, "_vb" & i & "$&")
End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Derek Johansen" wrote:

I have a spreadsheet with a lot of information. I then separate them into
separate workbooks to make it easier to manage. I generally create three new
workbooks from the data. what I would like to do is Save As each of these
new work books IN THE SAME DIRECTORY as the original and with the file name
ORIGINALNAME_wb1, ORIGINALNAME_wb2 etc.

The important part is that the files are in the same directory, and contain
the original name in them... does anyone have a bit of code i could use to do
this? I've played around with the save as, but I can't figure out how to get
the same directory as the original and add on to the name... any help would
be much appreciated!

Thanks,

Derek

Save As in a Macro
 

I'm not sure I am able to follow this enough to know where my inputs would
be. What would I need to change to customize the name of each book?

"r" wrote:

Function NewName(i As Long) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = "\.[A-z]+$"
NewName = ThisWorkbook.Path & _
Application.PathSeparator & _
RE.Replace(ThisWorkbook.Name, "_vb" & i & "$&")
End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Derek Johansen" wrote:

I have a spreadsheet with a lot of information. I then separate them into
separate workbooks to make it easier to manage. I generally create three new
workbooks from the data. what I would like to do is Save As each of these
new work books IN THE SAME DIRECTORY as the original and with the file name
ORIGINALNAME_wb1, ORIGINALNAME_wb2 etc.

The important part is that the files are in the same directory, and contain
the original name in them... does anyone have a bit of code i could use to do
this? I've played around with the save as, but I can't figure out how to get
the same directory as the original and add on to the name... any help would
be much appreciated!

Thanks,

Derek

Save As in a Macro
 

Sub test_1()
Dim i As Long
Dim s As String
For i = 1 To 3
s = NewName(i)
MsgBox s
Next
End Sub

Function NewName(i As Long) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = "\.[A-z]+$"
NewName = ThisWorkbook.Path & _
Application.PathSeparator & _
RE.Replace(ThisWorkbook.Name, "_vb" & i & "$&")
End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Derek Johansen" wrote:

I'm not sure I am able to follow this enough to know where my inputs would
be. What would I need to change to customize the name of each book?

"r" wrote:

Function NewName(i As Long) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = "\.[A-z]+$"
NewName = ThisWorkbook.Path & _
Application.PathSeparator & _
RE.Replace(ThisWorkbook.Name, "_vb" & i & "$&")
End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Derek Johansen" wrote:

I have a spreadsheet with a lot of information. I then separate them into
separate workbooks to make it easier to manage. I generally create three new
workbooks from the data. what I would like to do is Save As each of these
new work books IN THE SAME DIRECTORY as the original and with the file name
ORIGINALNAME_wb1, ORIGINALNAME_wb2 etc.

The important part is that the files are in the same directory, and contain
the original name in them... does anyone have a bit of code i could use to do
this? I've played around with the save as, but I can't figure out how to get
the same directory as the original and add on to the name... any help would
be much appreciated!

Thanks,

Derek