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]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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)?
 
Upvote 0
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"
 
Upvote 0
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?
 
Upvote 0
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 :)
 
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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