VBA to import sheet with name + todays date

[Dgenty]

Hi all,

I am using the below VBA to import another excel sheet and then delete the original from its location. I need to modify it to be a static name plus the date in the this format "22_03_2018" which will update everyday to current day.

So what is in the below now called "Download.xls" will be called "Download22_03_2018.xls". Can anyone help?

Sub CopySheets1()

Dim WB As Workbook
Dim SourceWB As Workbook
Dim WS As Worksheet
Dim ASheet As Worksheet
Dim aFile As String


Application.ScreenUpdating = False
Application.EnableEvents = False


Set WB = ActiveWorkbook
Set ASheet = ActiveSheet
Set SourceWB = Workbooks.Open("C:\Users\National Routing\Downloads" & "\Download.xls")




For Each WS In SourceWB.Worksheets
WS.Copy After:=WB.Sheets(WB.Sheets.Count)
Next WS

SourceWB.Close savechanges:=False
Set WS = Nothing
Set SourceWB = Nothing

WB.Activate
ASheet.Select
Set ASheet = Nothing
Set WB = Nothing

aFile = "C:\Users\National Routing\Downloads\Download.xls"
If Len(Dir$(aFile)) > 0 Then
Kill aFile
End If


Application.DisplayAlerts = False
Sheets("Vans").Select
Columns("H:cc").Select
Selection.Replace What:="Dummy", Replacement:="Download", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Comms").Select

Application.ScreenUpdating = True
Application.EnableEvents = True


Sheets("Comms").Select


Call main_macro


End Sub

 

[Joe4]

You can dynamically calculate the file name. I like to use variables to store the calculated field, and then you can simply just use that variable wherever you like in your code, i.e.

Dim fname as String
fname = "[COLOR=#333333]Download" & Format(Date,"dd_mm_yyyy") & ".xls"[/COLOR]