filename in Macro's

WayneJ

New Member
Joined
Oct 9, 2002
Messages
27
Hi once again, My problem is that I have an excel ROSTER put out by our roster clerk for viewing on the network.

I have created a Macro to change fonts, sizes, formatting, cutting and pasting several parts of the roster into a new sheet so we can print out only the parts we require. All this works fine, but this is where it gets interesting.

Our roster clerk changes the filname each month to something like this: 011102_ROSTERCALC.xls. The last part will not change only the date which is entered and I cannot predict what that date will be.

I need to be able to make a macro which I can INPUT the date manually, and this will replace the date already in the macro with the new one. Incidentally because in the macro there are 2 x cut and paste functions obviously the date appears twice so the macro would have to look for and replace the filename twice. I can change the dates myself within the macro but not everyone at my work could be trusted to go into the macro and do the same and it is quite messy to let them do this.

Any ideas would be appreciated.

Wayne
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Hi hope this gets you at least started, will resave all as existing to new name

HTH
JITUK

Sub SaveAs_Required()
'Designed by JackintheUK (No Conf Reqd)
'******* Saves file to name of whats input via message box &
'******* the ending *ROSTERCALC.xls
' ------ Example of format 011102_ROSTERCALC.xls
On Error GoTo TheEnd
Jacks_Input_Date = _
InputBox("Please input date of file name to save as!")
Filename = Jacks_Input_Date & "_ROSTERCALC.xls"
ThisWorkbook.SaveAs Filename:=Filename
Exit Sub
TheEnd:
'End

End Sub
 

WayneJ

New Member
Joined
Oct 9, 2002
Messages
27
Thanks Jack,

I'll give it a go and let you know how it went.


Wayne
 

WayneJ

New Member
Joined
Oct 9, 2002
Messages
27
Well Jack, Thanks again for the macro. However, this is not quite what I am looking for. Although I can use it in my Spreadsheet.

What I need is a macro to change the date (INSIDE) of another macro which I already run. See copy of macro BELOW. Both of the dates in this macro I need to replace with the date a user enters. ie:(291102_ROSTERCALC.xls)with (011202_ROSTERCALC.xls)



Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/11/2002 by Wayne
'

'
Windows("291102_ROSTERCALC.xls").Activate
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 73
Range("A11:AE78").Select
Range("AE78").Activate
Selection.Copy
Windows("Roster Format1.xls").Activate
ActiveWindow.SmallScroll ToRight:=-36
Range("A3").Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 52
Range("A71").Select
Windows("291102_ROSTERCALC.xls").Activate
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=74
ActiveWindow.SmallScroll ToRight:=7
ActiveWindow.SmallScroll Down:=17
ActiveWindow.SmallScroll ToRight:=13
Range("A94:AE113").Select
Range("AE113").Activate
Selection.Copy
Windows("Roster Format1.xls").Activate
ActiveSheet.Paste
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=21
Range("AF4").Select
Application.CutCopyMode = False
ActiveWindow.ScrollRow = 78
Range("A2:AE91").Select
Range("AE91").Activate
With Selection.Font
.Name = "Small Fonts"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection.Font
.Name = "Small Fonts"
.Size = 4
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With

End Sub
This message was edited by waynej on 2002-11-09 21:49
 

Forum statistics

Threads
1,144,218
Messages
5,723,069
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top