Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
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
Join the community of 500,000 technology professionals and ask your questions.