Copy from 1:Many WBs

Question

    Sub CopyPasteData()
    
    DataDir = "C:\My Documents\Test\"
    ChDir (DataDir)
    File = Dir("*.xlsm")

 
    While Nextfile <> ""                                               

        For Each f MasterWB.Names("nameList").RefersToRange   
            If f = File Then                                 

                newValues = MasterWB.Sheets("Master").Range("L4:U4").Value
                Workbooks.Open (File)
                Workbooks(Nextfile).Sheets("Report1").Unprotect Password:="qwedsa"


                Workbooks(Nextfile).Sheets("Report1").Range("H10:R10") = newValues
                Workbooks(File).Protect Password:="qwedsa"
                Workbooks(File).Save
                Workbooks(File).Close

            End If

        Next fileCell

        Nextfile = Dir()

    Wend

End Sub

I can't seem to iterate through the named range and appropriately copy and paste the lookup values between workbooks. I'm not sure how to make my loops work. How do I make this code to work?

Does this answer your question? [So, I have 6 "master" files to then divide into 40 separate files](https://stackoverflow.com/questions/30575923/so-i-have-6-master-files-to-then-divide-into-40-separate-files) — Solar Mike, Dec 21 '22 at 15:55
So, have a look at this: https://stackoverflow.com/q/40690592/4961700 Both can be edited to suit your needs... — Solar Mike, Dec 21 '22 at 16:21
I tried to reference that Q&A but it seems that my code is not matching the file name with the range list from the master workbook. What can be done to fix it? I have updated the original post with revised code — newbieLeaf, Dec 21 '22 at 16:56

score 1 · Accepted Answer · answered Dec 21 '22 at 17:59

Instead of opening every file and then checking to see if it's in your list, you can loop over the list and check to see if there's a matching file, and only then open and update it.

Sub CopyandPasteData()
    
    Const PW As String = "qwedsa" 'use constants for fixed values
    Dim fldr As String, wbMaster As Workbook, wsMaster As Worksheet
    Dim c As Range, wb As Workbook

    fldr = "C:\My Documents\Test\"
    
    Set wbMaster = ActiveWorkbook
    Set wsMaster = wbMaster.Worksheets("Master")
    
    For Each c In wsMaster.Range("B9:B111").Cells
        If Len(Dir(fldr & c.Value)) > 0 Then          'file exists?
            Set wb = Workbooks.Open(fldr & c.Value)
            With wb.Sheets("Report1")
                .Unprotect Password:=PW    'unprotect sheet and copy data
                .Range("H10:R10").Value = wsMaster.Range("L4:U4").Value
                .Protect Password:=PW
            End With
            wb.Close savechanges:=True
            ' ### fix the line below to reference the correct range ###
            c.Value = wbMaster.Worksheets("sheetName").Range("B4").Value
        End If
    Next c

End Sub

What actually happens when you copy the values from a 10-cell row to an 11-cell row? — VBasic2008, Dec 21 '22 at 22:37
@VBasic2008 - good catch. The 11th value in the destination will be #N/A, so at least that should be obvious... — Tim Williams, Dec 21 '22 at 22:41

Copy from 1:Many WBs

1 Answers1