I am trying to paste data from one Excel worksheet in a master workbook into up to 30 other workbook-CodePudding

I am trying to copy data from a master file and then paste it unchanged into workbooks already open on a loop. It keeps getting stuck at the bolded section. Hopefully someone can help. TIA

Sub Update_Files()

Dim WB As Workbook
Dim WS As Worksheet
Dim Master As Workbook

Set Master = ThisWorkbook

ThisWorkbook.Sheets("FX").Range("A1:I148").Select
Selection.Copy

For Each WB In Application.Workbooks
 If WB.Name <> "Master File.xlsb" Then
   **If WS.Name = WB.Sheets("FX Rates") Then
       'Paste FX data from Master
        Range("A1").PasteSpecial xlpastevalues
     End If
 End If
Next WB

CodePudding user response：

As commented, looks like you're missing a loop for your worksheets. Currently, you're only looping through your workbooks, not worksheets.

Added a loop to address this. Code loops through each open workbook, checks name. If Name <> "Master File.xlsb", then it continues to the next loop. This second loop iterates over each worksheet in current workbook, and checks if it's name equals "FX Rates", if it does, it pastes copied selection and continues the loop.

There are however much more VBA-ish way to achieve the same thing, but this answers your question. It compiles and runs just fine.

Sub Update_Files()
    
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim Master As Workbook
    
    Set Master = ThisWorkbook
    
    ThisWorkbook.Sheets("FX").Range("A1:I148").Select
    Selection.Copy

    For Each WB In Application.Workbooks
        If WB.Name <> "Master File.xlsb" Then
            For Each WS In WB.Worksheets
                If WS.Name = "FX Rates" Then
                    'Paste FX data from Master
                    WS.Range("A1").PasteSpecial xlPasteValues
                End If
            Next WS
        End If
    Next WB

End Sub