I have found the following code and it works great; however, I have to manually change the month so it goes to the right sheet on the second workbook. As the sheets are in months, how can I make it change automatically to the current month?
Sub AlarmSheet() Dim wkb As Workbook, wks As Worksheet, LastRow As Long Dim FilePath As String, FileName As String Dim ws As Worksheet, blnOpened As Boolean 'Change these variables as desired... FilePath = "B:\CARECENT\28 Day\" 'change path here FileName = "Installs_Removals_2016.xlsm" 'change name here Call ToggleEvents(False) Set ws = ThisWorkbook.Sheets("SHA") 'change source sheet name here If WbOpen(FileName) = True Then Set wkb = Workbooks(FileName) blnOpened = False Else If Right(FilePath, 1) <> Application.PathSeparator Then FilePath = FilePath & Application.PathSeparator End If Set wkb = Workbooks.Open(FilePath & FileName) blnOpened = True End If Set wks = wkb.Sheets("June") 'change destination sheet name here LastRow = wks.Cells.Find(what:="*", after:=wks.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1 wks.Cells(LastRow, "A").Value = ws.Cells(63, "B").Value wks.Cells(LastRow, "B").Value = ws.Cells(6, "B").Value wks.Cells(LastRow, "C").Value = ws.Cells(7, "B").Value wks.Cells(LastRow, "I").Value = ws.Cells(9, "B").Value '& "," & ws.Cells(10, "B").Value & "," & ws.Cells(11, "B").Value wks.Cells(LastRow, "J").Value = ws.Cells(10, "B").Value wks.Cells(LastRow, "N").Value = ws.Cells(11, "B").Value wks.Cells(LastRow, "O").Value = ws.Cells(15, "B").Value wks.Cells(LastRow, "P").Value = ws.Cells(60, "B").Value If blnOpened = True Then wkb.Close SaveChanges:=True End If 'If MsgBox("Clear values?", vbYesNo, "CLEAR?") = vbYes Then 'Call ClearData End If Call ToggleEvents(True) End Sub 
