new workbook add and save by a modified name based on original fil

[anduare2]

I have a single sheet i want to copy to a new workbook. (copy/paste/workbook
add - macro not a problem).

But i want to name and save the new workbook with the new filename based on
the old filename to a different location (it will be a hard coded location -
non changing) and with an added descriptor to the filename.

Such as, original filename is: JBSite New Location.xls
and it is opened from some sharepoint location of no importance. I have the
macro to copy a single page named Labels from this workbook to a new
workbook, but i need the original filename variables and code to correctly
create the workbook and name and save it from the first workbook JBSite New
Location with "Labels" appended to the final file name.

Sheets("Labels").Select
Cells.Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:= _

"http://mycompanywebsite.org/department1/docs/subfoldername/Forms/JBSite New
Location Labels.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

*with JBSite New Location being the variable filename
*with Labels being hardcoded and appended

the sheet to be copied from will always be named Labels in the original
book, the filename JBSite New Location will always be different. and the end
descripter Labels will always be Labels.

hope this makes sense, if there is a cleaner way to code all of this that is
fine. But i just record macro's and try to make them work. I am not a VB
programmer by any stretch of the imagination. Thanks

 

[Joel]

One trick in copying a worksheet to a new workbook is to use sheet.copy and
don't include before or after. This wsill create a workbook with only the
one sheet you copied and not contain any macros.

NewFolder = "http://mycompanywebsite.org/department1/docs/" & _
"subfoldername/Forms/"
FName = ActiveWorkbook.Name

Sheets("Label").Copy
Set NewBk = ActiveWorkbook

NewBk.SaveAs Filename:=NewFolder & FName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False


*with JBSite New Location being the variable filename
*with Labels being hardcoded and appended