This is all on Excel. I also am an amateur with VBA

Context: I have multiple workbooks (reference as "sub workbooks") that are all the same templates, consisting of the same type of data, but will be inputted with varying information on each.

I want to have all of these workbooks to be linked to a main excel to act as a tracker. On these "sub workbooks", if I input data, I'd like it to be inputted as well onto the tracker (I understand to use a basic link or "="). But speaking of the sub workbooks, the main thing is that I will need to clear the contents of the data and input a new set of data again, which should also be added onto the tracker. When that happens, the data is changed on the main tracker.

Problem: So my question is if there is a way to create a link that inputs all data entries from the sub workbooks onto the main tracker, and that data will still remain even if I clear the contents on the sub workbooks and input a fresh set of data.

Example: Column A has a set of patterns in all sub workbooks. The master tracker must also then be inputted with the same set of patterns combined from all sub workbooks in Column A. Then, Column A is cleared and a new set of patterns in added in all the sub workbooks. The master tracker must keep the previous data and then add the new patterns from all the sub workbooks onto the next available line in Column A.

What I've tried: The manual approach is to simply copy and paste the data from each sub workbook onto the master tracker. This takes a lot of time when dealing with >100,000 data entries. I've also tried to automate this manual approach with VBA to just extract the data from an XLSX of my choice, but my code doesn't seem to be very efficient and always keeps the first row (which is heading's). Would Powershell be an option, my VBA code to be tweaked, or another way to skip this extraction approach?

My VB

Dim ZZZmaster As Worksheet 'ZZZ references the name of the specific sheet to extract
Dim YYYmaster As Worksheet 'YYY references the name of the specic sheet to extract
Dim ZZZtemplates As Worksheet
Dim YYYtemplates As Worksheet
Dim filepath As String
    
    
    If MsgBox("Update ZZZ?", vbYesNo) = vbYes Then

    Set ZZZmaster = ThisWorkbook.Sheets("ZZZ_Bulk")

    
        filepath = Application.GetOpenFilename("Excel Files(*.xls;*.xlsx), *.xls;*.xlsx", , "Select the updated Report")
               If filepath = "False" Then Exit Sub ' User Cancelled

    
    Set ZZZtemplates = Workbooks.Open(filepath).Sheets("ZZZ_Bulk")

    ZZZtemplates.Range("A1").CurrentRegion.Copy Destination:=ZZZmaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        ActiveWorkbook.Close
    
    
    Else
        
        If MsgBox("Update YYY?", vbYesNo) = vbYes Then
        
            Set YYYmaster = ThisWorkbook.Sheets("YYY_Bulk")

    
            filepath = Application.GetOpenFilename("Excel Files(*.xls;*.xlsx), *.xls;*.xlsx", , "Select the updated Report")
               If filepath = "False" Then Exit Sub ' User Cancelled
    
             Set YYYtemplates = Workbooks.Open(filepath).Sheets("YYY_Bulk")

                 YYYtemplates.Range("A1").CurrentRegion.Copy Destination:=YYYmaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                      ActiveWorkbook.Close
                      
        Else
            End If
            
    End If
    
End Sub

This is all on Excel. I also am an amateur with VBA

Context: I have multiple workbooks (reference as "sub workbooks") that are all the same templates, consisting of the same type of data, but will be inputted with varying information on each.

I want to have all of these workbooks to be linked to a main excel to act as a tracker. On these "sub workbooks", if I input data, I'd like it to be inputted as well onto the tracker (I understand to use a basic link or "="). But speaking of the sub workbooks, the main thing is that I will need to clear the contents of the data and input a new set of data again, which should also be added onto the tracker. When that happens, the data is changed on the main tracker.

Problem: So my question is if there is a way to create a link that inputs all data entries from the sub workbooks onto the main tracker, and that data will still remain even if I clear the contents on the sub workbooks and input a fresh set of data.

Example: Column A has a set of patterns in all sub workbooks. The master tracker must also then be inputted with the same set of patterns combined from all sub workbooks in Column A. Then, Column A is cleared and a new set of patterns in added in all the sub workbooks. The master tracker must keep the previous data and then add the new patterns from all the sub workbooks onto the next available line in Column A.

What I've tried: The manual approach is to simply copy and paste the data from each sub workbook onto the master tracker. This takes a lot of time when dealing with >100,000 data entries. I've also tried to automate this manual approach with VBA to just extract the data from an XLSX of my choice, but my code doesn't seem to be very efficient and always keeps the first row (which is heading's). Would Powershell be an option, my VBA code to be tweaked, or another way to skip this extraction approach?

My VB

Dim ZZZmaster As Worksheet 'ZZZ references the name of the specific sheet to extract
Dim YYYmaster As Worksheet 'YYY references the name of the specic sheet to extract
Dim ZZZtemplates As Worksheet
Dim YYYtemplates As Worksheet
Dim filepath As String
    
    
    If MsgBox("Update ZZZ?", vbYesNo) = vbYes Then

    Set ZZZmaster = ThisWorkbook.Sheets("ZZZ_Bulk")

    
        filepath = Application.GetOpenFilename("Excel Files(*.xls;*.xlsx), *.xls;*.xlsx", , "Select the updated Report")
               If filepath = "False" Then Exit Sub ' User Cancelled

    
    Set ZZZtemplates = Workbooks.Open(filepath).Sheets("ZZZ_Bulk")

    ZZZtemplates.Range("A1").CurrentRegion.Copy Destination:=ZZZmaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        ActiveWorkbook.Close
    
    
    Else
        
        If MsgBox("Update YYY?", vbYesNo) = vbYes Then
        
            Set YYYmaster = ThisWorkbook.Sheets("YYY_Bulk")

    
            filepath = Application.GetOpenFilename("Excel Files(*.xls;*.xlsx), *.xls;*.xlsx", , "Select the updated Report")
               If filepath = "False" Then Exit Sub ' User Cancelled
    
             Set YYYtemplates = Workbooks.Open(filepath).Sheets("YYY_Bulk")

                 YYYtemplates.Range("A1").CurrentRegion.Copy Destination:=YYYmaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                      ActiveWorkbook.Close
                      
        Else
            End If
            
    End If
    
End Sub

• excel
• vba
• powershell
• automation

• If you're entering 100k rows at a time then copying to a master workbook isn't going to work for very long - you will quickly run out of space there. – Tim Williams Commented 2 days ago
• @TimWilliams good point, would like to clarify that number would not be per extraction process. Would ball park the data entries to be more around the 15-20k a month across multiple workbooks. When the workbook is nearly full, I'd assume to create another workbook to cover the same process again. These master workbooks would most likely be an annual accumulation. – user30124787 Commented 2 days ago
• It might be easier to save the incremental data set as separate files in the same location, and then compile them as needed. – Tim Williams Commented 2 days ago
• @TimWilliams I've also thought about that. I assume the process would be to just create a unique folder where they can be saved and input the path to the macro. Do you have any recommendation on what part I can tweak with my code to account for the folder input/extraction and if there are any ways I can speed up the execution - the run time is quite long? Thanks – user30124787 Commented 2 days ago
• It's difficult to suggest the "best" approach when it's not too clear what your actual use case is? I'm not following your posted code example. – Tim Williams Commented 2 days ago

1 Answer 1

If the only real problem is that you don't want to copy the headers:

Dim rngCopy As Range

Set rngCopy = ZZZtemplates.Range("A1").CurrentRegion.Offset(1) 'skip headers
rngCopy.Copy Destination:=ZZZmaster.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)