Link to home
Start Free TrialLog in
Avatar of vestanpance_uk
vestanpance_uk

asked on

Match Worksheet Name In Different Workbook and copy data over.

Hi Experts,

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..

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

Open in new window


I hope i've made sense and not waffled too much, i wanted to explain as best i could...

Thanks in advance..
Daz
Avatar of ragnarok89
ragnarok89

Your code seems perfect. If you're getting extra sheets when you shouldn't, can you give me an example of the name of a sheet that gets created but shouldn't, and the name of the existing sheet that should have been selected?
Avatar of vestanpance_uk

ASKER

Hi ragnarok89,

Thanks for your reply....
Sadly my code isn't perfect as it copies every sheet from the SourceWB  to a single Sheet in the DestinationWB which isn't what i wanted. (I don't get any extra sheets being added though.)

Basically, when the SourceWB is open i would like to copy the data from it's separate Worksheets to Worksheets of the same name in the DestinationWB.

For example.

SourceWB.Sheet1 -> Copy Sheet1 data
goto DestinationWB
DestinationWB.Sheet1 -> Check for the last row and drop down 1 -> paste data from SourceWB.Sheet1
goto SourceWB
SourceWB.Sheet2 -> Copy Sheet2 data
goto DestinationWB
DestinationWB.Sheet2 -> Check for the last row and drop down 1 -> paste data from SourceWB.Sheet2
goto SourceWB
SourceWB.Sheet3 -> Copy Sheet3 data
goto DestinationWB
etc etc.....

This goes on until all Sheets in SourceWB have been processed. There are over 80 Worksheets in the DestinationWB and generally there are 30 Worksheets in SourceWB on any given day and the Worksheet names i wish to copy are nearly always different... (as different Network Switches can have issues)

My original macro hard coded the names of the worksheets and it works fine, but as i said before i have found newer switches making their way into the Syslog list and they're getting missed off the DestinationWB (Central Workbook)

Thanks
Daz
Daz, you want something like this?

Sub CopyOver()
Dim DestinationWB As Workbook, OriginWB As Workbook
Dim LR As Long
Dim sht As Worksheet
Dim sCurrentSheet As String

'assign active origin workbook to variable
Set OriginWB = ActiveWorkbook

'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")

Application.ScreenUpdating = False

For Each sht In OriginWB.Worksheets
    If SheetExistsInWorkbook(sht.Name, DestinationWB) Then
        sht.Range("A1:F" & sht.Cells(Rows.Count, 1).End(xlUp).Row).Copy _
            DestinationWB.Sheets(sht.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Else
        sht.Copy After:=DestinationWB.Sheets(DestinationWB.Sheets.Count)
    End If
Next sht

Application.ScreenUpdating = True
End Sub


Function SheetExistsInDestinationWorkbook(strShtName As String, wb As Workbook) As Boolean
Dim sht As Worksheet

For Each sht In wb.Sheets
    If sht.Name = strShtName Then
        SheetExistsInDestinationWorkbook = True
        Exit Function
    End If
Next

SheetExistsInDestinationWorkbook = False

End Function

Open in new window





Thomas
Sorry, the name of the function should be updated. Here is the full code you should use
Sub CopyOver()
Dim DestinationWB As Workbook, OriginWB As Workbook
Dim LR As Long
Dim sht As Worksheet
Dim sCurrentSheet As String

'assign active origin workbook to variable
Set OriginWB = ActiveWorkbook

'This is the Workbook where i'd like the data to be copied to.
Set DestinationWB = Workbooks("Book5") 'Workbooks.Open("C:\Documents and Settings\dtayl211\Desktop\Syslog\New Folder\Syslog.xls")

Application.ScreenUpdating = False

For Each sht In OriginWB.Worksheets
    If SheetExistsInWorkbook(sht.Name, DestinationWB) Then
        sht.Range("A1:F" & sht.Cells(Rows.Count, 1).End(xlUp).Row).Copy _
            DestinationWB.Sheets(sht.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Else
        sht.Copy After:=DestinationWB.Sheets(DestinationWB.Sheets.Count)
    End If
Next sht

Application.ScreenUpdating = True
End Sub

Function SheetExistsInWorkbook(strShtName As String, wb As Workbook) As Boolean
Dim sht As Worksheet

For Each sht In wb.Sheets
    If sht.Name = strShtName Then
        SheetExistsInWorkbook = True
        Exit Function
    End If
Next

SheetExistsInWorkbook = False

End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot Thomas, that's twice you've come to my rescue!  That's absolutely spot on, exactly what i wanted it to do....

Thanks so much!
Daz
Glad to help. Sorry for the multiple posts.

Thomas