Macro to open last file and then perform actions

joeydeacan

New Member
Joined
Mar 26, 2014
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Good morning all,

I have created two macros which work indepdantly, but I really cannot work out how to put them together:-

The first basically opens the last saved csv, the next is just a macro that hides some columns, autosums and sorts, but i cannot work out how to get them to work with just the click of the first button ?

Can anyone help ?

1)

VBA Code:
Option Explicit

Sub NewestFile()

    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
   
    MyPath = "C:\Users\Documents\"
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    MyFile = Dir(MyPath & "*.xls", vbNormal)
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop
    Workbooks.Open MyPath & LatestFile
End Sub


2)
VBA Code:
Sub RemoveunusedData90F()
'
' RemoveunusedData90F Macro
' Remove unused Data - 90F
'
' Keyboard Shortcut: Ctrl+r
'
    Range("F:F,G:G,H:H,I:I,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W"). _
        Select
    Range("W1").Activate
    ActiveWindow.LargeScroll ToRight:=1
    ActiveWindow.SmallScroll ToRight:=-5
    Columns("X:X").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Columns("K:BO").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 77
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 81
    ActiveWindow.ScrollColumn = 82
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 90
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 105
    ActiveWindow.ScrollColumn = 106
    ActiveWindow.ScrollColumn = 107
    ActiveWindow.ScrollColumn = 108
    ActiveWindow.ScrollColumn = 109
    ActiveWindow.ScrollColumn = 110
    ActiveWindow.ScrollColumn = 111
    ActiveWindow.ScrollColumn = 112
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 114
    ActiveWindow.ScrollColumn = 115
    ActiveWindow.ScrollColumn = 116
    ActiveWindow.ScrollColumn = 117
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 119
    ActiveWindow.ScrollColumn = 120
    ActiveWindow.ScrollColumn = 121
    ActiveWindow.ScrollColumn = 122
    ActiveWindow.ScrollColumn = 123
    ActiveWindow.ScrollColumn = 124
    ActiveWindow.ScrollColumn = 125
    ActiveWindow.ScrollColumn = 126
    Columns("CT:ET").Select
    Range("ET1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 82
    ActiveWindow.ScrollColumn = 80
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 67
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 74
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 77
    ActiveWindow.ScrollColumn = 78
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 80
    ActiveWindow.ScrollColumn = 81
    ActiveWindow.ScrollColumn = 82
    ActiveWindow.ScrollColumn = 83
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 87
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 90
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 96
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 87
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 83
    ActiveWindow.ScrollColumn = 82
    ActiveWindow.ScrollColumn = 81
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 74
    ActiveWindow.ScrollColumn = 75
    ActiveWindow.ScrollColumn = 76
    ActiveWindow.ScrollColumn = 77
    ActiveWindow.ScrollColumn = 78
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 80
    ActiveWindow.ScrollColumn = 81
    ActiveWindow.ScrollColumn = 82
    ActiveWindow.ScrollColumn = 83
    Range("EU2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(RC[-83]:RC[-54])"
    Range("EU2").Select
    Selection.AutoFill Destination:=Range("EU2:EU54"), Type:=xlFillDefault
    Range("EU2:EU54").Select
    Range("A2:EU54").Select
    Range("EU54").Activate
    ActiveWorkbook.Worksheets("Player_Data_2020-7-18_7.42.53").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Player_Data_2020-7-18_7.42.53").Sort.SortFields. _
        Add2 Key:=Range("EU2:EU54"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Player_Data_2020-7-18_7.42.53").Sort
        .SetRange Range("A1:EU54")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("BP:CS").Select
    Range("CS35").Activate
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
End Sub
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

joeydeacan

New Member
Joined
Mar 26, 2014
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
I cant see where i can delete this post, I think im being a bit dim !

I think what a really need is the first part (opening csv) to actually copy the CSV into sheet2 ? How would i change the below to do that ?

1)

VBA Code:
Option Explicit

Sub NewestFile()

   Dim MyPath As String
   Dim MyFile As String
   Dim LatestFile As String
   Dim LatestDate As Date
   Dim LMD As Date
  
   MyPath = "C:\Users\Documents\"
   If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
   MyFile = Dir(MyPath & "*.CSV", vbNormal)
   If Len(MyFile) = 0 Then
       MsgBox "No files were found...", vbExclamation
       Exit Sub
   End If
   Do While Len(MyFile) > 0
       LMD = FileDateTime(MyPath & MyFile)
       If LMD > LatestDate Then
           LatestFile = MyFile
           LatestDate = LMD
       End If
       MyFile = Dir
   Loop
   Workbooks.Open MyPath & LatestFile
End Sub
 
Last edited by a moderator:

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
.
If in fact both macros function as desired, the easiest thing would be a call to the second macro from within the first macro.
At the bottom of the first macro, you can make the call like this :

VBA Code:
Do While Len(MyFile) > 0
LMD = FileDateTime(MyPath & MyFile)
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If
MyFile = Dir
Loop
Workbooks.Open MyPath & LatestFile

RemoveunusedData90F

End Sub

The first thing you should do however, is to edit your posts so the macro code is displayed properly and per the FORUM rules. There is a MENU SELECTION
in the response window that applies the proper formatting. The symbol looks like: <vba/> .
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
@Logit
As the OP is a new member he/she cannot edit posts. ;)
 

Forum statistics

Threads
1,148,274
Messages
5,745,796
Members
423,973
Latest member
man_this_is_hard

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