Splitting up information into separate tabs

Mellzer

New Member
Joined
Aug 6, 2018
Messages
8
[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]
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,821
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 6, 2018
Messages
8
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,821
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 6, 2018
Messages
8

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,821
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 6, 2018
Messages
8

ADVERTISEMENT

It always starts with no suffix, max is 4.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,821
Office Version
  1. 365
Platform
  1. Windows
Here is a little macro that should open the correct file:
Code:
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

New Member
Joined
Aug 6, 2018
Messages
8
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,821
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,817
Messages
5,542,666
Members
410,567
Latest member
SCraig123
Top