VBANovices
New Member
- Joined
- Jan 15, 2018
- Messages
- 4
Hi
I have built a macro process sheet with 6 different macros on it that run the same function across 6 pairs of sheets each day (and I am very much a novice) to run some essential daily processes for me where the macro takes a formula from one excel sheet (SheetA) and it gets pasted into a separate new daily sheet (SheetB) and then some filtering and column hiding takes place to give me a saved sheet each day. This as I say operates across 6 pairs of sheets so it would be Sheets C and D etc for Method2
The issue I have is SheetB (and the other 5 daily sheets) are downloaded from a website each day and come appended with the date on the end so for example it is sheetB-2018-01-15.xlsx for 15th jan but them sheetB-2018-01-16 the next day
I couldn't get the macro to work unless I changed the date to a constant file name so each day I download the sheets and I have to manually delete the date and I replace it with Method1 ( and then so on Method2, Method3 etc for the other 5 sheets)
I am sure this is a long winded way to do it the first part of the file name will always remain constant it is just this date
Can anybody at all help me with a solution on how to rename the files easily or for the macro to point to the correct file with the date changing each day on the end
I copy the macro code for one of these methods here and it is the text in red where this dated sheet would fit in with affects two places in the macro when it opens and activates the sheet and when it closes and saves it, as I say I manually change it to Method1 each day to get the macro to work
Sub Method1_HT_CS_Lay_Process()
'
' Method1_HT_CS_Lay_Process Macro
'
'
MsgBox "Close All Open Excel Sheets Except This One and All Other Windows Please"
Workbooks.Open ("C:\Users\Administrator\Dropbox\Trading Days 2018\Formula Sheet & Daily Process HT CS Lays 6 Games.xlsx")
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="C:\Users\Administrator\Dropbox\Trading Days 2018\Formula Sheet & Daily Process HT CS Lays 6 Games.xlsx")
' Do stuff
wb.Activate
Range("DK1:DN2").Select
Selection.Copy
Workbooks.Open("C:\Users\Administrator\Dropbox\Trading Days 2018\DailyFiles\correctscore_halftime_6games-Method1.xlsx").Activate
Range("DK2").Select
ActiveSheet.Paste
Range("DK3:DN3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("DK3:DN237"), Type:=xlFillDefault
Range("DK3:DN237").Select
Range("DF2").Select
ActiveCell.FormulaR1C1 = "BF Price"
Range("DF2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("DG:DJ").Select
Selection.EntireColumn.Hidden = True
Columns("I:DE").Select
Range("DE1").Activate
Selection.EntireColumn.Hidden = True
ActiveSheet.Range("$A$2:$CX$237").AutoFilter Field:=3, Criteria1:=Array( _
"Belgian Premier League", "Bundesliga 1", "Dutch Eredivisie", _
"Dutch Jupiler League", "English Championship", "English Premier League", _
"French Ligue 1", "German Bundesliga 2", "Polish Ekstraklasa", "Primera Division", _
"Serie A", "Eliteserien", "Russian Premier League", "Turkish Super League"), Operator:=xlFilterValues
ActiveSheet.Range("$A$2:$CX$237").AutoFilter Field:=8, Criteria1:=">=3", _
Operator:=xlAnd
Workbooks("correctscore_halftime_6games-Method1.xlsx").Close SaveChanges:=True
Workbooks("Formula Sheet & Daily Process HT CS Lays 6 Games.xlsx").Close SaveChanges:=False
MsgBox "Your HT CS Lay Selections have been filtered for todays games that may qualify. The file *****correctscore_halftime_6games-Method1.xlsx**** has been saved in your Daily Files folder.Please check all BET CRITERIA before placing a bet on these games"
End Sub
Grateful of any help
Regards
Ian
I have built a macro process sheet with 6 different macros on it that run the same function across 6 pairs of sheets each day (and I am very much a novice) to run some essential daily processes for me where the macro takes a formula from one excel sheet (SheetA) and it gets pasted into a separate new daily sheet (SheetB) and then some filtering and column hiding takes place to give me a saved sheet each day. This as I say operates across 6 pairs of sheets so it would be Sheets C and D etc for Method2
The issue I have is SheetB (and the other 5 daily sheets) are downloaded from a website each day and come appended with the date on the end so for example it is sheetB-2018-01-15.xlsx for 15th jan but them sheetB-2018-01-16 the next day
I couldn't get the macro to work unless I changed the date to a constant file name so each day I download the sheets and I have to manually delete the date and I replace it with Method1 ( and then so on Method2, Method3 etc for the other 5 sheets)
I am sure this is a long winded way to do it the first part of the file name will always remain constant it is just this date
Can anybody at all help me with a solution on how to rename the files easily or for the macro to point to the correct file with the date changing each day on the end
I copy the macro code for one of these methods here and it is the text in red where this dated sheet would fit in with affects two places in the macro when it opens and activates the sheet and when it closes and saves it, as I say I manually change it to Method1 each day to get the macro to work
Sub Method1_HT_CS_Lay_Process()
'
' Method1_HT_CS_Lay_Process Macro
'
'
MsgBox "Close All Open Excel Sheets Except This One and All Other Windows Please"
Workbooks.Open ("C:\Users\Administrator\Dropbox\Trading Days 2018\Formula Sheet & Daily Process HT CS Lays 6 Games.xlsx")
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="C:\Users\Administrator\Dropbox\Trading Days 2018\Formula Sheet & Daily Process HT CS Lays 6 Games.xlsx")
' Do stuff
wb.Activate
Range("DK1:DN2").Select
Selection.Copy
Workbooks.Open("C:\Users\Administrator\Dropbox\Trading Days 2018\DailyFiles\correctscore_halftime_6games-Method1.xlsx").Activate
Range("DK2").Select
ActiveSheet.Paste
Range("DK3:DN3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("DK3:DN237"), Type:=xlFillDefault
Range("DK3:DN237").Select
Range("DF2").Select
ActiveCell.FormulaR1C1 = "BF Price"
Range("DF2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("DG:DJ").Select
Selection.EntireColumn.Hidden = True
Columns("I:DE").Select
Range("DE1").Activate
Selection.EntireColumn.Hidden = True
ActiveSheet.Range("$A$2:$CX$237").AutoFilter Field:=3, Criteria1:=Array( _
"Belgian Premier League", "Bundesliga 1", "Dutch Eredivisie", _
"Dutch Jupiler League", "English Championship", "English Premier League", _
"French Ligue 1", "German Bundesliga 2", "Polish Ekstraklasa", "Primera Division", _
"Serie A", "Eliteserien", "Russian Premier League", "Turkish Super League"), Operator:=xlFilterValues
ActiveSheet.Range("$A$2:$CX$237").AutoFilter Field:=8, Criteria1:=">=3", _
Operator:=xlAnd
Workbooks("correctscore_halftime_6games-Method1.xlsx").Close SaveChanges:=True
Workbooks("Formula Sheet & Daily Process HT CS Lays 6 Games.xlsx").Close SaveChanges:=False
MsgBox "Your HT CS Lay Selections have been filtered for todays games that may qualify. The file *****correctscore_halftime_6games-Method1.xlsx**** has been saved in your Daily Files folder.Please check all BET CRITERIA before placing a bet on these games"
End Sub
Grateful of any help
Regards
Ian