With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
Sub CopyOver() Dim DestinationWB As Workbook Dim LR As Long Dim Sht As Worksheet Dim sCurrentSheet As String 'LastRow - Start from the bottom and go up to find the last row of data LR = Cells(Rows.Count, "A").End(xlUp).Row 'Rename the title of the workbook that's currently open to SourceWorkbook 'as the WB name wil be different every day. ActiveWorkbook.Windows(1).Caption = "SourceWorkbook" 'This is the Workbook where i'd like the data to be copied to. Set DestinationWB = Workbooks.Open("C:\Documents and Settings\dtayl211\Desktop\Syslog\New Folder\Syslog.xls") 'Activate the workbook with the data i want to copy over Windows("SourceWorkbook").Activate 'Store the name of the current sheet so i can reference it against the sheet name in the Destination workbook. sCurrentSheet = Windows("SourceWorkbook").ActiveSheet.Name Application.ScreenUpdating = False For Each Sht In Application.Worksheets Sht.Activate 'Select Range from Column A1 through to Column F down to the last row Range("A1:F" & LR).Select 'Copy the text, dont cut it. Application.CutCopyMode = False Selection.Copy 'Switch over to the Destination Workbook - Syslog.xls Windows("Syslog.xls").Activate 'Activate the current sheet name found from the Source Workbook, so the WSheet is the same. Sheets(sCurrentSheet).Activate 'tell me what sheet you think should be activated 'msgbox sCurrentSheet 'Find the last used cell and drop down one as i don't want to overwrite any rows. Range("A65536").End(xlUp).Offset(1, 0).Select 'Paste in the information. ActiveSheet.Paste 'Flip back to the Source Workbook so i can get the information from the next worksheet Windows("SourceWorkbook").Activate Next Sht Application.ScreenUpdating = True End Sub