Macro to open last file and then perform actions

J

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
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:
Upvote 0
.
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/> .
 
Upvote 0
@Logit
As the OP is a new member he/she cannot edit posts. ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

An Quala
  • Question
Should I name it Active.Sheet or sheet name?
Replies
8
Views
627
MARK858
MARK858
V
  • Solved
make specific workbook macrorecording work with any workbook
Replies
7
Views
432
Fluff
Fluff
E
  • Solved
Macro to scroll page side to side
Replies
1
Views
2K
mikerickson
mikerickson
togenmoser
  • Solved
Finding all possible combinations of 2 sums
2
Replies
10
Views
185
togenmoser
togenmoser
S
  • Solved
Make macro smaller
Replies
4
Views
444
Fluff
Fluff

Forum statistics

Threads
1,214,391
Messages
6,119,247
Members
448,879
Latest member
oksanana

Share this page

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
Back
Top