I've looked through various answers on here and i've even tried some of the solutions and despite the solution working for other peoples problems, i've been unable to change the code around to suit my needs, which brings me here..
Every day we receive an excel file from our Tools team.
In that Workbook is a single sheet which contains Syslog messages for various network switches. I have managed to create a macro that will take the Switch Name, create a Worksheet with that switches name and then copies/pastes every row of data that relates to that particular switch onto the associated Worksheet. It then adds 'Todays date' so we know when this issue was acknowledged.
Put simply, it distributes the information for each switch, along with the date to it's own Worksheet, which is exactly what i want.
I would then like to copy each Worksheet from this Workbook to a Central Workbook.
The problem is the Central Workbook (Syslog.xls) already has these Worksheet names (because i added them), so i don't want to overwrite the information that is already on these Worksheets, merely add to them.
I already have a solution, but it's very clunky in that i have it look to the SourceWB, activate Sheet1 (for example) copy all data, Switch over to DestinationWB (Syslog.xls), activate Sheet1, find last used row, drop down to the last row, paste data, Switch back to SourceWB, go to Sheet2 etc etc....
My problem is that i've found more sheets being created (as Network switches are being added) than i have allowed, so i'm looking for a more dynamic way of copying the sheets over.
I have come up with the following code, however it doesn't work as i hoped it might and i could really do with some help.
I have come up with the following code, but clearly it didn't work the way i hoped it would.
I always comment my code so i know what it's doing (or think i know what it's doing) so i hope that it helps explain my thought process..
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
'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
'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
'Switch over to the Destination Workbook - Syslog.xls
'Activate the current sheet name found from the Source Workbook, so the WSheet is the same.
'tell me what sheet you think should be activated
'Find the last used cell and drop down one as i don't want to overwrite any rows.
'Paste in the information.
'Flip back to the Source Workbook so i can get the information from the next worksheet
Application.ScreenUpdating = True
I hope i've made sense and not waffled too much, i wanted to explain as best i could...
Thanks in advance..