MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|VB.Net Exporting Excel Woorkbook to PDF||7||58|
|Formula returning #N/A||9||31|
|Excel VLookup in a macro where returned column changes every month||7||13|
|Excel - Data Validation - Dynamic Weekday||11||23|