Macro

[Guest]

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("C1618").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

 

[Crowbar via OfficeKB.com]

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

 

[Guest]

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.