Public Sub Data() Dim wbk As Workbook Dim Filename As String Dim Path As String Dim sht, msht As Worksheet Dim lRowFile, lRowMaster As Long Dim FirstDataSet As Integer On Error Resume Next Path = "C:\Users\VBA\" FirstDataSet = 2 'First Data Set in File 'First Workbook Filename = "Brand1.xlsx" Set wbk = Workbooks.Open(Path & Filename) Set sht = Workbooks(Filename).Worksheets("Raw") 'First Sheet in File Set msht = ThisWorkbook.Worksheets("msh_Brand1") 'First Sheet in Master lrF = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'Last Row in File lRM = msht.Cells(Rows.Count, 2).End(xlUp).Row 'Last Row in Master For i = FirstDataSet To lrF lRM = msht.Cells(Rows.Count, 2).End(xlUp).Row 'Last Row in Master msht.Range("B" & lRM + 1).Value = sht.Range("A" & i).Value 'ID msht.Range("C" & lRM + 1).Value = sht.Range("B" & i).Value 'Name msht.Range("E" & lRM + 1).Value = sht.Range("C" & i).Value 'Store msht.Range("F" & lRM + 1).Value = sht.Range("D" & i).Value 'Product Code msht.Range("I" & lRM + 1).Value = sht.Range("F" & i).Value 'Brand msht.Range("J" & lRM + 1).Value = sht.Range("G" & i).Value 'Form msht.Range("K" & lRM + 1).Value = sht.Range("H" & i).Value 'Days msht.Range("L" & lRM + 1).Value = sht.Range("I" & i).Value 'Category Next sht.Range("K2:AZZ" & lrF).Copy _ Destination:=msht.Range("M2") wbk.Close True 'Second Workbook Filename = "Brand2.xlsm" Set wbk = Workbooks.Open(Path & Filename) Set sht = Workbooks(Filename).Worksheets("Raw") 'First Sheet in File Set msht = ThisWorkbook.Worksheets("msh_Brand2") 'Second Sheet in Master lrF = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'Last Row in File lRM = msht.Cells(Rows.Count, 2).End(xlUp).Row 'Last Row in Master For i = FirstDataSet To lrF lRM = msht.Cells(Rows.Count, 2).End(xlUp).Row 'Last Row in Master msht.Range("A" & lRM + 1).Value = sht.Range("B" & i).Value 'ID msht.Range("G" & lRM + 1).Value = sht.Range("A" & i).Value 'Name Next sht.Range("C2:AZZ" & lrF).Copy _ Destination:=msht.Range("N2") wbk.Close True End Sub
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)