Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
new workbook add and save by a modified name based on original fil
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
new workbook add and save by a modified name based on original fil
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 "anduare2" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'Trick' to restore original sort order after data modified? | Excel Discussion (Misc queries) | |||
Move one ws to new wb. Save new Wb with same but modified name as original WB | Excel Programming | |||
Auto save replaced my original file and now I need the original? | Excel Discussion (Misc queries) | |||
Save original workbook name, referenced by macros in other books? | Excel Programming | |||
How to get the original data in a cell after it is modified | Excel Programming |