Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro
Hello
I am new to macros and have recorded my first one. I have a problem gettng it to do what I want to. I have several sheets linked to to data in another sheet. One I have the data on the sheets I want to paste as values, which I did. I also want to link to another file and then break the link, which I did. Now the problem. I have multiple files I want this template to work on, so when I save the file I use save as ( the macro retaines the last file name. I want to be able to give it another name each time I run the macro. The macro also retains the link from the last time I run the macro, how do I fix to to be able to be able to save as a new file and change the link name. I am attaching the macro to see if you can help. Any help will be greatly appreciated. THANK YOU. Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B12:F12").Select Sheets("Licensed Beds").Select Range("F13:I15").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("J16").Select ActiveSheet.Next.Select Range("B15:L34").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B15:C15").Select Application.CutCopyMode = False ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("Administrator Detail").Select ActiveWindow.LargeScroll Down:=-1 Range("C12:O17").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C12:G12").Select Application.CutCopyMode = False ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("Highest Paid Employee").Select Range("C16:P18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C16:F16").Select Application.CutCopyMode = False ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("SqFt").Select Range("F11:F50").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("F13").Select Application.CutCopyMode = False Range("G11").Select ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-2 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWindow.ScrollWorkbookTabs Sheets:=-1 ActiveWorkbook.BreakLink Name:= _ "H:\RSC\RSC2006\HCF-1 Completed\Southpointe.001 2006.xls", Type:=xlExcelLinks ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets(Array("HCFLIST", "Perm", "Cost Report Contact")).Select Sheets("HCFLIST").Activate ActiveWindow.SelectedSheets.Delete Range("D33").Select ActiveWindow.LargeScroll Down:=-1 Range("D16").Select ActiveWindow.LargeScroll Down:=-1 Range("D16").Select ChDir "C:\2007 Client Package" ActiveWorkbook.SaveAs Filename:= _ "C:\2007 Client Package\jones 2007 Client Package.xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False End Sub -- thank you mac |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro
Hello, I can help
I think that you are saying you need to automatically generate a unique filename when you save. Before doing this you may want to consider how the file name will differ ie If you save a new one each day, you could start the filename with the same description and then end it with the date. This would create uniformity within your directory and can be applyed within your script. Dim MyDate As Date MyDate = Date ChDir "C:\2007 Client Package" ActiveWorkbook.SaveAs Filename:= _ "C:\2007 Client Package\jones 2007 Client Package" & Format(MyDate, "dd-mmm-yy") &".xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200710/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro
Thank you for your help. My problem is that I have to give each file a
different file name by client, I do not need a date. How do I get the macro to ask me to give it a file name. Again thank for any help you can give me. -- thank you mac "Crowbar via OfficeKB.com" wrote: Hello, I can help I think that you are saying you need to automatically generate a unique filename when you save. Before doing this you may want to consider how the file name will differ ie If you save a new one each day, you could start the filename with the same description and then end it with the date. This would create uniformity within your directory and can be applyed within your script. Dim MyDate As Date MyDate = Date ChDir "C:\2007 Client Package" ActiveWorkbook.SaveAs Filename:= _ "C:\2007 Client Package\jones 2007 Client Package" & Format(MyDate, "dd-mmm-yy") &".xls", FileFormat:=xlNormal _ , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200710/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |