I am new to VBA coding and working on automating a process that I perform every month. I recorded the macro below which works as intended if I am testing using the specific specified filename. I need both the Capital One Allocation & transaction download filenames to be variable. I have searched the boards and was unable to find something similar enough for me to try and use it. This is called from a user form:
Any help you can give would be appreciated.
VBA Code:
Private Sub Yes_Click()
Windows("Capital One Allocation - Rev IW.xlsm").Activate
Windows("2020-11-29_transaction_download.xlsx").Activate
'Copy range
Range("A2:G100").Select
Selection.Copy
Windows("Capital One Allocation - Rev IW.xlsm").Activate
Range("B3").Select
ActiveSheet.Paste
Range("B2:H53").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Download").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Download").Sort.SortFields.Add2 Key:=Range( _
"D3:D53"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Download").Sort.SortFields.Add2 Key:=Range( _
"B3:B53"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Download").Sort
.SetRange Range("B2:H53")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B3").Select
Unload DownloadImport
End Sub
Any help you can give would be appreciated.