Compare and update master worksheets if they exist in another workbook?

Question

I have a master workbook, which houses a group of 15 worksheets that house data for summary pivot tables and whatnot. Every week this master workbook gets updated with a daily report that has those 15 worksheets, but also around 20 other ones. I am just trying to get a script together to identify if they exist, and if so, to move that daily data to the master workbooks worksheet (only move data if daily wb worksheet exists in master workbook).

Here is a very general shell of what I'm trying to achieve, but I'm not well versed in determining the logic if a sheet exists, so my blnFound variable is obviously misplaced. I hope this shows a rough outline of what I'm trying to achieve. Any help is greatly appreciated!

Option Explicit
Sub Update_New_Data()
    Const BasePath As String = "C:\\User\Data..."

    Dim wbMaster As Workbook: Set wbMaster = ThisWorkbook
    Dim wbNewData As Workbook: Set wbNewData = Workbooks.Open(BasePath & "\03.01.20.xlsx")
    Dim wsMaster As Sheet
    Dim blnFound As Boolean

    'places all sheet names into array
    With wbNewData
        Dim varWsName As Variant
        Dim i As Long
        Dim ws As Worksheet
        ReDim varWsName(1 To wbNewData.Worksheets.Count - 2)
            For Each ws In wbNewData.Worksheets
                Select Case ws.Name
                    Case "Inputs", "Data --->>>"
                    Case Else
                        i = i + 1
                        varWsName(i) = ws.Name
                End Select
            Next
    End With

    'if wbNewData sheet name is found in wbMaster
    'then locate it and place wbNewData data into that sheet
    With wbMaster
        For Each wsMaster In wbMaster.Sheets
            With wsMaster
                If .Name = varWsName(i) Then
                    blnFound = True
                    wbNewData(Worksheets(i)).UsedRange.Copy Destination:=wbMaster(Worksheets(i)).Range("A1")
                Else: blnFound = False
                End If
            End With
        Next
    End With


End Sub

Perhaps [this](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) is useful. — BigBen, Mar 02 '20 at 18:03
@BigBen I did see these functions in my initial research, but since my request is different from the other ones on stack, I wasn't really sure where to put it in my code — NidenK, Mar 02 '20 at 18:05

CDP1802 · Accepted Answer · 2020-03-03T18:11:25.707

1

To check if something exists you can use a Dictionary Object

Option Explicit
Sub Update_New_Data()

    Const BasePath As String = "C:\\User\Data..."

    Dim wbMaster As Workbook, wbNewData As Workbook
    Set wbMaster = ThisWorkbook
    Set wbNewData = Workbooks.Open(BasePath & "\03.01.20.xlsx", , False) ' read only
    Dim ws As Worksheet, sKey As String, rng As Range, msg As String

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    'places all master sheet names into dictionary
    For Each ws In wbMaster.Sheets
        If ws.Name = "inputs" Or ws.Name = "Data --->>>" Then
            ' skip
        Else
            dict.Add CStr(ws.Name), ws.Index
            Debug.Print "Added to dict", ws.Index, ws.Name
        End If
    Next

    ' if wbNewData sheet name is found in wbMaster
    ' then locate it and place wbNewData data into that sheet
    For Each ws In wbNewData.Sheets
        sKey = CStr(ws.Name)
        If dict.exists(sKey) Then
            ' clear master
            wbMaster.Sheets(dict(sKey)).cells.clear
            Set rng = ws.UsedRange
            rng.Copy wbMaster.Sheets(dict(sKey)).Range("A1")
            msg = msg & vbCr & ws.Name
        Else
            Debug.Print "Not found in master", ws.Index, ws.Name
        End If
    Next
    wbNewData.Close

    ' result
    If Len(msg) > 0 Then
        MsgBox "Sheets copied were " & msg, vbInformation
    Else
        MsgBox "No sheets copied", vbExclamation
    End If

End Sub

edited Mar 03 '20 at 18:11

answered Mar 02 '20 at 19:04

CDP1802

13,871
2
7
17

incredible, this is going to end up being SO helpful. Thank you so much! – NidenK Mar 02 '20 at 19:28
Hey, I have a quick question. Everytime I run this, I will need to clear the cell contents in the masterwb sheet I am updating. I know I would just need to add in a `ws.cells.clear` in there, but where exactly would I put that? – NidenK Mar 03 '20 at 17:35
Or would I need to add in a new for loop like : For Each ws In masterwb sKey = CStr(ws.Name) If dict.exists(sKey) Then ws.Cells.Clear Else End If Next – NidenK Mar 03 '20 at 17:37
@NidenK Just put in in the first loop before Debug.Print "Added to dict", ws.Index, ws.Name – CDP1802 Mar 03 '20 at 17:40
So I notice that it's clearing a bunch of other summary sheets, which I don't want to happen. The only sheets I want it to clear are the ones where the `wbNewData` match the `wbMaster`, nothing else. When the `dict.add ws.name` part happens, it adds all the names (including the summary sheets that link to the data I'm refreshing). So what I tried was to switch the two loops around, but then when I clear the cells, it sets the `rng` to what I just cleared and doesn't pull in the `wbNewData` – NidenK Mar 03 '20 at 18:07
not sure if that made it more confusing. Basically, there are 50 other sheets in the `wbMaster` workbook that are summary tabs w/ formulas and I can't clear the contents, but I also don't want to list out every single sheet to avoid. Is there an easy way around this without explicitly listing it out? – NidenK Mar 03 '20 at 18:12
@NidenK Ok, I updated my code - inside the second loop – CDP1802 Mar 03 '20 at 18:12

Compare and update master worksheets if they exist in another workbook?

1 Answers1