Splitting up information into separate tabs

[Mellzer]

[FONT=&quot]I have this macro that copies several files into a master spreadsheet. All of the files names remain the same except one, the date constantly changes day to day. How can i do this? Here is one example of my code.

Workbooks.Open Filename:= _

"\\barracuda\sls_depts\DEPTS\SRG\Logistics\INVENTORY\CSG HALLMARK MASTER INVENTORY\AISLES\AISLE 7 JEWELRY CABINET.xlsx"

Range("B2:J3000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Windows("AISLE 7 JEWELRY CABINET.xlsx").Activate
ActiveWindow.Close


This is the location and file name of the file, the name always remains the same except for the date. If its updated several times a day it has the same date but -2 or -3 for the number of times its been updated. Ex (8-3-18-2)

"\\barracuda\SLS_Depts\DEPTS\<wbr>SRG\Logistics\INVENTORY\9991 & 9998 DC ON HAND FOR 8-3-18.xlsx"[/FONT][FONT=&quot]

[/FONT]

 

[Joe4]

This is the location and file name of the file, the name always remains the same except for the date. If its updated several times a day it has the same date but -2 or -3 for the number of times its been updated. Ex (8-3-18-2)

So at any point in time, is there only one file with today's date in the folder, or multiple ones?
If multiple ones, are you trying to open ALL of them, or just a specific one (and if so, which one)?

 

[Mellzer]

Yes, there are other files in the folder with a date. but none are named this bold part of the file im trying to import "9991 & 9998 DC ON HAND FOR 8-3-18.xlsx"

 

[Joe4]

Sorry, that is not quite what I am getting at.

This is the location and file name of the file, the name always remains the same except for the date. If its updated several times a day it has the same date but -2 or -3 for the number of times its been updated. Ex (8-3-18-2)

"\\barracuda\SLS_Depts\DEPTS\<wbr style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">SRG\Logistics\INVENTORY\9991 & 9998 DC ON HAND FOR 8-3-18.xlsx"

So, if I understand you correctly, it sounds like there could be file names like:
\\barracuda\SLS_Depts\DEPTS\<wbr style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">SRG\Logistics\INVENTORY\9991 & 9998 DC ON HAND FOR 8-3-18.xlsx
\\barracuda\SLS_Depts\DEPTS\<wbr style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">SRG\Logistics\INVENTORY\9991 & 9998 DC ON HAND FOR 8-3-18-2.xlsx
\\barracuda\SLS_Depts\DEPTS\<wbr style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">SRG\Logistics\INVENTORY\9991 & 9998 DC ON HAND FOR 8-3-18-3.xlsx

Is that correct?
What I am asking is whether or not there can be multiple files (with the names shown above) in the folder at the same time?
If so, do you want to open all three of them, or just one? If just one, which one should we open?

 

[Mellzer]

Yes, its possible that there could be multiple files like you have shown above. I only want the newest of the file to be loaded

 

[Joe4]

Yes, its possible that there could be multiple files like you have shown above. I only want the newest of the file to be loaded

Does it always start off with the first file have no suffix, and then the second has "-2", the third "-3", etc?
What is the maximum number you may have (does it over get above 9 for a particular day)?

 

[Mellzer]

It always starts with no suffix, max is 4.

 

[Joe4]

Here is a little macro that should open the correct file:

Sub MyOpenFile()

    Dim myPrefix As String
    Dim myDate As String
    Dim mySuffix As Long
    Dim myFName As String
    Dim i As Integer
    Dim foundFile As Boolean
    
'   Enter file path and prefix
    myPrefix = "\\barracuda\SLS_Depts\DEPTS\SRG\Logistics\INVENTORY\9991 & 9998 DC ON HAND FOR"
    
'   Capture current date in desired format
    myDate = Format(Date, "m-d-yy")
    
'   Initialize foundFile
    foundFile = False
    
'   Loop through file names backwards, starting at 4
    For i = 4 To 2 Step -1
'       Build full file name
        myFName = myPrefix & " " & myDate & "-" & i & ".xlsx"
'       Open file, it exists
        If Dir(myFName) <> "" Then
            Workbooks.Open Filename:=myFName
            foundFile = True
            Exit For
        End If
    Next i


'   If no files with suffixes of 2-4, open base file
    If foundFile = False Then
        myFName = myPrefix & " " & myDate & ".xlsx"
        Workbooks.Open Filename:=myFName
    End If
    
End Sub

 

[Mellzer]

This opens fantastically!

Im trying to add this at the end and it copys into the tab but not on the next available line. And then i need the file to close after its been copied.

Range("A2:J20000").Select
Selection.Copy
Windows("OHCompare.xlsx").Activate
Sheets("OnHand").Select
ActiveSheet.Paste
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
End If
End Sub

 

[Joe4]

So what issue exactly are you having with your copy code?
If you are trying to put it on the next available row, you need to switch the "ActiveSheet.Paste" line with your "Range...Select" line.

Here is an amendment to the code that copies the data and closes the workbook it just opened.

Sub MyOpenFile()

    Dim myPrefix As String
    Dim myDate As String
    Dim mySuffix As Long
    Dim myFName As String
    Dim i As Integer
    Dim foundFile As Boolean
    Dim wb1 As Workbook
    
'   Enter file path and prefix
    myPrefix = "\\barracuda\SLS_Depts\DEPTS\SRG\Logistics\INVENTORY\9991 & 9998 DC ON HAND FOR"
    
'   Capture current date in desired format
    myDate = Format(Date, "m-d-yy")
    
'   Initialize foundFile
    foundFile = False
    
'   Loop through file names backwards, starting at 4
    For i = 4 To 2 Step -1
'       Build full file name
        myFName = myPrefix & " " & myDate & "-" & i & ".xlsx"
'       Open file, it exists
        If Dir(myFName) <> "" Then
            Workbooks.Open Filename:=myFName
            Set wb1 = ActiveWorkbook
            foundFile = True
            Exit For
        End If
    Next i

'   If no files with suffixes of 2-4, open base file
    If foundFile = False Then
        myFName = myPrefix & " " & myDate & ".xlsx"
        Workbooks.Open Filename:=myFName
        Set wb1 = ActiveWorkbook
    End If
    
'   Copy data
    wb1.Range("A2:J20000").Copy
    Windows("OHCompare.xlsx").Activate
    Sheets("OnHand").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    
'   Close workbook
    wb1.Close
    
End Sub