0

I am writing a sub to aggregate about 40 production schedules into one workbook. My code listed below should be finding an open workbook with a name like all schedules (wild card used to account for date) and then creating a union range with the fields that I need aggregated. However, when building the union range, the selection corresponds to my "master schedule" ranges rather than that week's schedule, even though that is the active workbook. This code runs off of a button on the master schedule and gives me this issue, however if I run it from the vb console in the individual schedule, then it works. I am not sure what the issue is here. 

    Sub CP_Data()
    Dim WorkCenter, Process_Order, Mat_Num, Batch_In, Qty_Needed, Desc, MRP, 
    Union_Range As Range
    Dim lRow, lRow2 As Long
    Dim wb As Workbook
    Dim wbName As String
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    lRow2 = Cells(Rows.Count, 2).End(xlUp).Row



    Set WorkCenter = ActiveSheet.Range("F3", "F" & lRow)
    Set Process_Order = ActiveSheet.Range("K3", "K" & lRow)
    Set Mat_Num = ActiveSheet.Range("M3", "M" & lRow)
    Set Batch_In = ActiveSheet.Range("O3", "O" & lRow)
    Set Qty_Needed = ActiveSheet.Range("Q3", "Q" & lRow)
    Set Desc = ActiveSheet.Range("N3", "N" & lRow)
    Set MRP = ActiveSheet.Range("W3", "W" & lRow)


    WorkCenter.Copy
    Workbooks("Master_Schedule").Activate
    Range("A" & lRow2 + 1).PasteSpecial xlPasteValues

    wbName = "all schedules"

    For Each wb In Application.Workbooks

        If wb.Name Like wbName & "*" Then
            Windows(wb.Name).activate
            wb.Sheets("Paste").Columns.EntireColumn.Hidden = False
            wb.Sheets("Paste").Rows.EntireRow.Hidden = False
            Set WorkCenter = wb.Sheets("Paste").Range("F3", "F" & lRow)
            Set Process_Order = wb.Sheets("Paste").Range("K3", "K" & lRow)
            Set Mat_Num = wb.Sheets("Paste").Range("M3", "M" & lRow)
            Set Batch_In = wb.Sheets("Paste").Range("O3", "O" & lRow)
            Set Qty_Needed = wb.Sheets("Paste").Range("Q3", "Q" & lRow)
            Set Desc = wb.Sheets("Paste").Range("N3", "N" & lRow)
            Set MRP = wb.Sheets("Paste").Range("W3", "W" & lRow)

          Set Union_Range = Union(WorkCenter, Mat_Num, Process_Order, Desc, Batch_In, Qty_Needed, MRP)

           Union_Range.Copy

          Workbooks("Master_Schedule").Activate
          Range("A" & lRow2 + 1).PasteSpecial xlPasteValues
        Else

        End If

    Next   

    End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
accortdr
  • 91
  • 11
  • 1
    What is `Windows(wb.Name).set`? Also, learning [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) (or `Activate`) should be of use. – BigBen Oct 29 '18 at 14:25
  • 2
    `wb.Sheets("Paste").Range("F3", "F" & lRow)`-lRow only refers to the original Rows count, it may not be the same for `wb.Sheets("Paste")` You may need to count the rows for each wb loop. – Davesexcel Oct 29 '18 at 14:51

0 Answers0