Move one ws to new wb. Save new Wb with same but modified name as original WB

B

BEEJAY

Greetings:
Looking to name a new WB with the (modified) name of the original WB.

The following code MOVES one worksheet to a new WB.
' MOVE "COM.CALC to NEW WorkBook
Sheets("COM.CALC.").Move

At this point the new WB is the active one.
I'm looking to get the (previously saved) file name from the original file,
and use it on the new file, with "-CC" added to the end.
The original file name will always be different. (its not a template with a
"fixed" name)
The path for the new WB always to be the same.
So new file to save as: C:\CONTRACTS COMMISH\OldFileName-CC.
Then the old file is to be closed without a save.

Thanks all in advance.....................
 
J

JLGWhiz

This has not been tested.

Assuming that the procedure is in the original workbook's code module:
Sub moveSheet()
Dim myPath As String, wbNewName As String
myPath = ThisWorkbook.Path
wbNewName = ThisWorkbook.Name & "my suffix"
Sheets("COM.CALC.").Move 'Not sure about the period after CALC
ActiveWorkbook.SaveAs FileName:=myPath & "\" & wbNewName
End Sub
 
B

BEEJAY

JLGWhiz, Thanks for your input.
I tried your code and it worked as expected.
However, the code module will not be in the original wb.
So the "new" file was saved as "personal.xls.-cc, in the XLSTART folder.
However, using your input and coding from another module I have, I now have
the following (tested)

Sub moveSheet()
'Make "Contract" the Active Sheet
Sheets("Contract").Select
Range("A1").Select

Dim OldName As String
Dim OldPath As String
Dim NewName As String
Dim NewPath As String
Application.DisplayAlerts = False
' Use Existing name, Modified by adding "-CC" at the end
OldPath = ActiveWorkbook.Path & "\"
OldName = ActiveWorkbook.Name

Sheets("COM.CALC.").Move
NewPath = "C:\CONTRACTS COMMISH\"
NewName = NewPath & Left(OldName, Len(OldName) - 4) & "-CC.xls"
ActiveWorkbook.SaveAs NewName
Application.DisplayAlerts = True
End Sub

Thanks for your input and please keep up working in this ng.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top