VBA - Copy and paste into multiple workbooks if cell = "Y"-CodePudding

I'm trying to write some code to do the following:

If a cell says "Y", then open workbooks x, a, b, c, and copy data from workbook x and paste into workbooks a, b, and c.

This will then check to see if another cell says "Y" and repeat the process with another set of workbooks.

I'm having difficulty with two things. First, I would like to open workbooks based on a filepath name that is in a cell. For instance, I can get the workbooks to open if I use set x = workbooks.open ("file path name here") but, if I instead put the filepath name in cell "A1" and try set x = workbooks.open ("A1") the macro won't work. Second, I have no idea how to get the code to skip over the operation if a certain cell isn't labeled "Y". You can see my attempt in the code but I tried several other things and keep running into issues.

The code I'm linking is just a sample, as my goal is to have this cycle through about 6 primary workbooks, each of the primary will then be copy and pasted into 3-5 sub workbooks.

Thanks in advance for any help on this!!

Sub Foo()
    Dim x As Workbook
    Dim y As Workbook
    Dim z As Workbook
    
    Dim a As Workbook
    Dim b As Workbook
    Dim c As Workbook
    Dim d As Workbook
    Dim e As Workbook
    Dim f As Workbook
    Dim g As Workbook
    
    Dim vals As Variant
    
    'skip these workbooks if N14 is "N"
    If wSheet.Range("N14") = "Y" Then
    
    '## Open workbooks:
    Set x = Workbooks.Open("A1")
    Set a = Workbooks.Open("A2")
    Set b = Workbooks.Open("A3")
    Set c = Workbooks.Open("A4")
    
    'Store the value in a variable:
    vals = x.Sheets("Processed Summary").Range("M5:M63").Value
    
    'Use the variable to assign a value to the other file/sheet:
    a.Sheets("Processed Summary").Range("L5:L63").Value = vals
    b.Sheets("Processed Summary").Range("L5:L63").Value = vals
    c.Sheets("Processed Summary").Range("L5:L63").Value = vals
    
    If wSheet.Range("N15") = "Y" Then
    Set y = Workbooks.Open("B1")
    Set d = Workbooks.Open("B2")
    Set e = Workbooks.Open("B3")
    Set f = Workbooks.Open("B4")
    
    
    'Store the value in a variable:
    vals = y.Sheets("Processed Summary").Range("M5:M63").Value
    
    'Use the variable to assign a value to the other file/sheet:
    d.Sheets("Processed Summary").Range("L5:L63").Value = vals
    e.Sheets("Processed Summary").Range("L5:L63").Value = vals
    f.Sheets("Processed Summary").Range("L5:L63").Value = vals
    
    'Close x:
    'x.Close
    'y.Close
    'z.Close
    'a.Close
    'b.Close
    'c.Close
    'd.Close
    'e.Close
    'f.Close
    'g.Close
    
End Sub

CodePudding user response：

You need to specify from where Range is originating. When code is trying to open second workbook "Set a = Workbooks.Open(Range("J6").Value)" it pulls Range out of cell J6 in workbook x as it is currently active workbook.

Try changing to: "Set a = Workbooks.Open(Workbooks("name.xls").Worksheets("Sheet1").Range("J6").Value)"