Every day I am manually cutting and pasting data from a daily report into this Master spread sheet. The new file is the same every day, only the name of the file is different each day because it adds the date. ex. 2017-03-11-18875, 2017-03-12-18875, 2017-03-13-18875 etc.
What I'm reading says I need to create a code to search for a new file, then open the file, cut the data out, and paste it into my existing spreadsheet.
This could need some debugging, depending on your filenames and other things. If when copying to your master sheet you go down to the first empty row to paste, then you will have to incorporate some code to get the first unused row number. Not a problem, let me know. The rest I tried to explain as I went along. If you have any questions let me know. Make a copy of your master workbook, and use that when practicing with this code. Open a module in the copied workbook and paste this code. See if you can follow the logic.
Sub getOpenExcel()
' Your daily report has a date in it's name
' to select an open workbook we must first know it's name
' AND - it must be already open
' Your examples are 2017-03-11-18875, 2017-03-12-18875, 2017-03-13-18875
' If the name is the current date then this would work to get the filename
Dim fileName As String, monthNum As String, dayNum As String, wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range, rng2 As Range
' this adds a ZERO to the front of month numbers less than 10
If Month(Date) < 10 Then
monthNum = "0" & CStr(Month(Date))
Else
monthNum = CStr(Month(Date))
End If
' You may or may not need this section
' it adds a ZERO to the front of day numbers less than 10
If Day(Date) < 10 Then
dayNum = "0" & CStr(Day(Date))
Else
dayNum = CStr(Day(Date))
End If
' many cases the daily report will come from the previous day
' If your file has yesterday's date, then comment out the above code and uncomment the following code
'
'If Day(DateAdd("d", -1, Date)) < 10 Then
' dayNum = "0" & Day(DateAdd("d", -1, Date))
'Else
' dayNum = Day(DateAdd("d", -1, Date))
'End If
fileName = CStr(Year(Date)) & "-" & monthNum & "-" & dayNum & "-" & "18875"
' if today's date is 3/14/17 then "fileNem" = "2017-03-12-18875"
' If your daily report is an excel book, then we need to add the proper extension.
' It could be one of many, "xls", ".xlsx" , ".xlsm", etc....
' If your daily report is open - look at the top. It should have the file name and extension.'
' Replace the below extension with the correct one.
fileName = fileName & ".xlsx"
' Again, if today's date is 3/14/17 then "fileNem" = "2017-03-12-18875.xlsx"
' This is where we set both workbooks to variables
'
Set wb1 = ThisWorkbook ' This is your master sheet
Set ws1 = wb1.Worksheets("Sheet1")
On Error GoTo notOpen
Set wb2 = Workbooks(fileName) ' This is your daily report
On Error GoTo 0
Set ws2 = wb2.Worksheets("Sheet1")
ws1.Activate
'*************************************************************************************
' If successful this is the area where you put your code to copy and paste automatically
'
' If you need this pasted to the first empty row at bottom of page then
' put code here to find the first empty row and use that varaible
' with range("a" & firstUnusedRow) intstead of A1 ...
wb2.Activate
Range("A1:Z500").Copy _
Destination:=wb1.Worksheets("Sheet1").Range("A1") 'change A1 to A & firstUnusedRow
'*************************************************************************************
' This is the clean up and exit code
Set wb1 = Nothing
Set wb2 = Nothing
Exit Sub
notOpen:
On Error GoTo 0
Set wb1 = Nothing
MsgBox "The file " & fileName & " is not open"
Exit Sub
End Sub