VBA, For Each Next - If sheet name in this workbook equals to a sheet name in another workbook

0
votes

I have two workbooks, some sheets have the same name. I would like to export (copy) some data from sheet A in Workbook TeamINSO to this workbook (where the code is) to the sheet with the same name and I'm stuck. How to write: loop through sheets in workbook TeamINSO, if sheet name equals a sheet name in this workbook, then copy range?

For Each ws In TeamINSO.Worksheets
    If ws.Name = ThisWorkbook.Worksheets(ws.Name) Then
        Workbooks(TeamINSO).Worksheets(ws.Name).Range("A3:C400").Copy
               ThisWorkbook.Worksheets(ws.Name).Range("A2").PasteSpecial xlPasteValues
    Else
    End If
Next ws

Thanks for helping.

1
excelvba

1 Answers

1
votes

You can do it like this:

Dim ws As Worksheet, wsD as Worksheet

For Each ws In TeamINSO.Worksheets
    'see if there's a match
    On Error Resume Next  'ignore error if no match
    Set wsD = ThisWorkbook.Sheets(ws.Name)
    On Error Goto 0       'stop ignoring errors
    'any match?
    If Not wsD Is Nothing Then
        'Transfer values 
        With ws.Range("A3:C400")
          wsD.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    End If

    Set wsD = Nothing 'set up for next iteration if any
Next ws