Macro to open last file and then perform actions

joeydeacan

New Member
Joined
Mar 26, 2014
Messages
6
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:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

joeydeacan

New Member
Joined
Mar 26, 2014
Messages
6
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
3,709
.
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
48,130
Office Version
  1. 365
Platform
  1. Windows
@Logit
As the OP is a new member he/she cannot edit posts. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,020
Messages
5,545,533
Members
410,690
Latest member
navneetr
Top