Activate a sheet based on sheet name from another file

Question

I have 2 files: a and b. Both have multiple sheets with the same sheet names. I want to copy data from b to a to matching sheets. My question is how to activate b file sheet that matches the active sheet name in a, so i don't have to write the name of the sheet everytime

something like this: Workbooks("b.xlsx").Activate Sheets("workbooks("a.xlsx").activesheet.name").Activate 'instead of writing sheets("sheet1").activate if sheet1 is the active sheet in file a' Range("A1:A5").select — cristi m, Jul 05 '19 at 15:49

score 1 · Accepted Answer · answered Jul 05 '19 at 16:00

Define your source and destination workbook
Loop through your source worksheets and copy

Something like this should work

Public Sub CopyBtoA()
    Dim wbSource As Workbook
    Set wbSource = Workbooks("fileB.xlsx")

    Dim wbDestination As Workbook
    Set wbDestination = Workbooks("fileA.xlsx")

    Dim ws As Worksheet
    For Each ws In wbSource.Worksheets
        ws.Range("A1").Copy Destination:=wbDestination.Worksheets(ws.Name).Range("A1")
    Next ws
End Sub

Note that this assumes that both files are already opened in Excel. Otherwise you need to open them with Workbooks.Open() like:

Set wbSource = Workbooks.Open Filename:="C:\your path\fileB.xlsx"

Don't use .Activate or .Select you don't need them! See How to avoid using Select in Excel VBA.

Note that I recommend to check if the worksheet exists in the destination workbook before you copy. Otherwise you will run into errors:

Public Sub CopyBtoA()
    Dim wbSource As Workbook
    Set wbSource = Workbooks("fileB.xlsx")

    Dim wbDestination As Workbook
    Set wbDestination = Workbooks("fileA.xlsx")

    Dim ws As Worksheet
    For Each ws In wbSource.Worksheets
        If WorksheetExists(ws.Name, wbDestination) Then
            ws.Range("A1").Copy Destination:=wbDestination.Worksheets(ws.Name).Range("A1")
        End If
    Next ws
End Sub

'check if a worksheet exists
Public Function WorksheetExists(ByVal WorksheetName As String, Optional ByVal wb As Workbook) As Boolean
    If wb Is Nothing Then Set wb = ThisWorkbook 'default to thisworkbook

    Dim ws As Worksheet
    On Error Resume Next
    Set ws = wb.Worksheets(WorksheetName)
    On Error GoTo 0

    WorksheetExists = Not ws Is Nothing
End Function

score 0 · Answer 2 · answered Jul 05 '19 at 22:33

0

Public Function Sheet_NameSake( _
        ByVal ws_Name As String, _
        wb_Dest As Workbook) _
        As Worksheet

    Set Sheet_NameSake = wb_Dest.Worksheets(ws_Name)

End Function

answered Jul 05 '19 at 22:33

InExSu VBAGem t2d2

86
5

Activate a sheet based on sheet name from another file

2 Answers2