How to paste data from a worksheet in a master workbook into up to 30 other workbooks using a loop?

Question

I am trying to copy data from a master file and then paste it into workbooks already open on a loop.

It gets stuck at

If WS.Name = WB.Sheets("FX Rates") Then

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

Have a look at this, and give it a vote: https://stackoverflow.com/q/30575923/4961700 — Solar Mike, Jun 07 '22 at 13:11

score 0 · Accepted Answer · answered Jun 07 '22 at 13:17

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

Also, make sure that your workbook is actually named with extension. I've found that it usually is not... — Havard Kleven, Jun 07 '22 at 13:17
Thank you, this worked. Just had to tweak something due to formatting and is all working now. — rmba22875, Jun 07 '22 at 13:34

How to paste data from a worksheet in a master workbook into up to 30 other workbooks using a loop?

1 Answers1