Weird VBA problems. Out of memory. F8 step through failing. crashing...

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
436
Hi guys. I did google this, at length, and found the answers not that helpful. Just FYI. ;)


I have a vba module thats about 28 pages long.
There are 5 of these modules.
Each module has around 100 declared variables.

I need to add more. About 10 more to each.

When I try to add more using
Code:
Dim xyx as integer
For example, Excel says "out of memory" and then usually crashes.

Also then the F8 "step through" isn't working. It just decides to run all the code.


Why is this happening? I have MUCH larger processes that do not do this.

Any suggestions? I work for a corporation and don't really have the ability to increase my RAM or mess with registry files. All that stuff is locked down.

Thanks! I can post the code if you want but I figured 28 pages is not ideal.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
436
Thanks for the replies. I sadly don't really think I can call this issue resolved at this point.

Some further explanation:
I have a workbook that contains 5 modules, each importing a series of excel files.
Each module is running an import for about 10-15 individually coded excel files. This workbook is automated to consolidate 75 manually fed excel spreadsheets into a single spreadsheet. (dreadful, i know) The files are not the same at all and many seem manually created.

So the modules were going to be way too long if done all in 1 or 2 modules so I broke it into 5. However, if the length is too long, then I will make it more than 5 batches. I will post the full code below as well even though its really long...


Code:
[/B]Option Explicit


Sub File_Uploads_1()


Dim i As Long
Dim lastRowIndex, last_row, x_row, target_last_row, nextrow, lastrow  As Long
Dim PO_NUM_collection, ITEM_NUM_collection, TRACKING_collection, Serials_collection, MTM_NUM_collection, CARRIER_collection, ARTICLE_NUM_collection, CONV_DATE_collection, CONV_QTY_collection, CONV_NUM_collection As String
Dim po_num_col, file_name_col, first_Row, item_num_col, TRACKING_col, article_num_col, conv_date_col, conv_quantity_col, target_column As Integer
Dim conv_number_col, MTM_num_col, Carrier_col, serials_num_col, error_rpts, Last_Col, Header_row, first_header, Header_col As Integer
Dim data_type, filename, folderpath, header_value As String
Dim wb, hopper As Workbook
Dim Hopper_data, target_data, source_sheet As Worksheet
Dim wb_name, One_off_WBs, ext, destinationpath, true_name, hopperpath, hoppername As String
Dim Last_Err_Rpt, firstrow As Integer
Dim fso As Object
Dim rLastCell As Range
Dim strFolder As String: strFolder = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Reports\"
Dim strFileSpec As String: strFileSpec = strFolder & "*.*"
Dim strFileName As String
Dim Target_col1, Target_col2, Target_col3, Target_col4, Target_col5, Target_col6, Target_col7, Target_col8, Target_col9, Target_col10 As Integer
Dim Source_col1, Source_col2, Source_col3, Source_col4, Source_col5, Source_col6, Source_col7, Source_col8, Source_col9, Source_col10 As Integer
Dim sourcebook As Workbook
Dim batch_num, Batch_col, error_count As Integer
Dim DCA, DCXX, order_num_col, Cust_po_num_col As Integer
Dim DCB, xrow As Long
Dim strExt As String
Dim batch_audit_Col, batch_audit_Col_end As Integer
Dim validation_counter, x As Integer




batch_num = 1
'checks to see if this batch has already been run on a given day.  If so, the option is given to cancel the batch run.
If Dash.Range("D2").Value = Date Then
            If MsgBox("Run Batch?", vbYesNo + vbQuestion) = vbNo Then
                        MsgBox "Cancelling Batch."
                        End
            Else
                        MsgBox "Running Batch."
            End If
Else
            MsgBox "Running Batch " & batch_num
End If


Application.DisplayAlerts = False
Application.CalculateBeforeSave = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = False


Dash.Range("I3:J100").ClearContents




Set fso = CreateObject("Scripting.FileSystemObject")


'sets the file path for key files
'opens "the hopper".
folderpath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Reports\"
hopperpath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\"
hoppername = "Automated Report Data Hopper.xlsx"
Set hopper = Workbooks.Open(hopperpath & hoppername)
Set Hopper_data = Workbooks(hoppername).Worksheets("Hopper")




If MsgBox("Clear Hopper Data?  If you are running batch one, the answer is generally yes.", vbYesNo + vbQuestion) = vbYes Then
            With Hopper_data
                    If .FilterMode = True Then .ShowAllData
                    DCA = Application.Match("PO_Nr", .Rows("1:1"), 0)
                    DCXX = Application.Match("Serial Numbers", .Rows("1:1"), 0)
                    DCB = .Columns(Application.Match("PO_Nr", .Rows("1:1"), 0)).Find("*", , , , xlRows, xlPrevious).Row
                    .Range(.Cells(2, DCA), .Cells(DCB + 1, DCXX)).ClearContents
            End With
End If


'Builds folder directory with current date in report archive folder location seen below
If Dir("I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Dated Report Archive\" & Format(Date, "yyyy-mm-dd"), vbDirectory) = "" Then
            MkDir ("I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Dated Report Archive\" & Format(Date, "yyyy-mm-dd") & "\")
End If
destinationpath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Dated Report Archive\" & Format(Date, "yyyy-mm-dd") & "\"


'sets data ranges inside "the hopper" file
With Hopper_data
            Target_col1 = Application.Match("PO_Nr", .Rows("1:1"), 0)
            Target_col2 = Application.Match("ItemNr", .Rows("1:1"), 0)
            Target_col3 = Application.Match("ArticleNr", .Rows("1:1"), 0)
            Target_col4 = Application.Match("ConvDate", .Rows("1:1"), 0)
            Target_col5 = Application.Match("ConvQuantity", .Rows("1:1"), 0)
            Target_col6 = Application.Match("ConvNumber", .Rows("1:1"), 0)
            Target_col7 = Application.Match("Manufpartnum", .Rows("1:1"), 0)
            Target_col8 = Application.Match("Carrier", .Rows("1:1"), 0)
            Target_col9 = Application.Match("Trackinginfo", .Rows("1:1"), 0)
            Target_col10 = Application.Match("Serial Numbers", .Rows("1:1"), 0)
End With




strFileName = Dir(strFileSpec)
With Hopper_data
            'big loop that processes the data from bout a dozen files and compile appends this data into "the hopper".
            Do While strFileName <> ""
loopsetter:
                        On Error GoTo nextsheet:
                        Source_col10 = ""
                        Source_col7 = ""
                        strExt = CreateObject("Scripting.FileSystemObject").GetExtensionName(strFileName)
                        If strExt = "xls" Or strExt = "XLS" Or strExt = "xlsx" Or strExt = "XLSX" Or strExt = "csv" Or strExt = "xlsb" Or strExt = "xlsm" Or strExt = "CSV" Or strExt = "XLSB" Or strExt = "XLSM" Then
                                    If InStr(1, strFileName, "Account Activity - TECH Data") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("TECH Data Activity - DO NOT REP")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("Customer Order#", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("Customer Order#", source_sheet.Rows(firstrow), 0)
                                                            Source_col2 = Application.Match("Position #", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("Date", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Ordered", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("3M Order #", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Item", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("Tracking #", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "Tracking - Tech Data - Tracking  Tech Data") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("Sheet1")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("Order #", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("Cust_PO", source_sheet.Rows(firstrow), 0)
                                                            Source_col2 = Application.Match("Pos", source_sheet.Rows(firstrow), 0)
                                                            Source_col3 = Application.Match("Cust. Item #", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("Dt. Shipped", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Qty", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("ItemNum", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("Tracking ", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col3), source_sheet.Cells(lastrow, Source_col3)).Copy Destination:=.Cells(nextrow, Target_col3)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "Lenovo Sea Shipments - CustomReport") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("Order list_1")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("Sales Order Line Number", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("Customer Purchase Order Number", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("Firm Ship Date", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Order Quantity", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("Sales Order Number", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Product ID", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Carrier Name", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("Carrier Tracking Number", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "Tech Data Open Order Book") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("Open Order Book")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("PARTNER PURCHASE ORDER", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("PARTNER PURCHASE ORDER", source_sheet.Rows(firstrow), 0)
                                                            Source_col2 = Application.Match("ITEM LINE", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("ESTIMATED SHIP DATE", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("QUANTITY", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("HP SALES ORDER", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("SKU", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                End If
                                                        
                                    ElseIf InStr(1, strFileName, "Tech Data US Ship Status US") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets(1)
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("Document N", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("Purchase O", source_sheet.Rows(firstrow), 0)
                                                            Source_col2 = Application.Match("Item Numbe", source_sheet.Rows(firstrow), 0)
                                                            Source_col3 = Application.Match("Customer M", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("Bill.date", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("ConfirmQty", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("Document N", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Material", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Forwarding Agent Name", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("Tracking Number/PRO", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col3), source_sheet.Cells(lastrow, Source_col3)).Copy Destination:=.Cells(nextrow, Target_col3)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "TechData Weekly Reports for") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("qryOOR_Partner_MasterReport")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("BillingPONumber", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("BillingPONumber", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("RevisedShipWeek", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("POQuantity", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("HowardOrder", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("POSKU", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("TrackingNumber", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "Tech Data Shipment report") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("qryTechData_ship")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("PO.", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("PO.", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("Ship Date", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Shipment.", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("Order.", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Part.", source_sheet.Rows(firstrow), 0)
                                                            Source_col10 = Application.Match("UNIT_SERIAL_NBR", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("Pro-Number-1", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col10), source_sheet.Cells(lastrow, Source_col10)).Copy Destination:=.Cells(nextrow, Target_col10)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "Buffalo Estimated Ship Dates") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("Sheet1")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("Customer PO Number", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("Customer PO Number", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("Estimated Shipment Week", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Qty", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Item", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "TechDataUSA_Shipment") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets(1)
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("cust_no", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("po_no", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("ship_date", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("ship_qty", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("so_no", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("part_no", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("carrier", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "TechDataUSA_BackLog") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets(1)
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("cust_name", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("po_no", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("etd", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("on_order", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("so_no", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("part_no", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "NEC DISPLAY") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets(1)
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("Customer Name", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("P.O. No.", source_sheet.Rows(firstrow), 0)
                                                            Source_col2 = Application.Match("Line #", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("Sched Dt", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Quantity", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("NEC Order", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Material No.", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("Bill of Lading", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "Daily Ship Report - TDUS") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("Shipping Report")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("PO#", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("PO#", source_sheet.Rows(firstrow), 0)
                                                            Source_col3 = Application.Match("SKU", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("SHIP DATE", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Shipped Qty", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("Order No", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Belkin Part No", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("Tracking Number", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col3), source_sheet.Cells(lastrow, Source_col3)).Copy Destination:=.Cells(nextrow, Target_col3)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "Daily Shipping Report 3741 TECH DATA CORP as of") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("Shipping Report")
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("PO#", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("PO#", source_sheet.Rows(firstrow), 0)
                                                            Source_col3 = Application.Match("SKU", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("SHIP DATE", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Shipped Qty", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("Order No", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Belkin Part No", source_sheet.Rows(firstrow), 0)
                                                            Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
                                                            Source_col9 = Application.Match("Tracking Number", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col3), source_sheet.Cells(lastrow, Source_col3)).Copy Destination:=.Cells(nextrow, Target_col3)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
                                                End If
                                                
                                    ElseIf InStr(1, strFileName, "Garmin Open Orders Report") > 0 Then
                                                Set wb = Workbooks.Open(folderpath & strFileName)
                                                Set source_sheet = Workbooks(strFileName).Worksheets("Sheet1")
                                                
                                                source_sheet.Columns("A:J").UnMerge
                                                
                                                nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                                                firstrow = Application.Match("Customer PO Number", source_sheet.Columns("B"), 0)
                                                lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
                                                order_num_col = Application.Match("Order Number", source_sheet.Rows(firstrow), 0)
                                                Cust_po_num_col = Application.Match("Customer PO Number", source_sheet.Rows(firstrow), 0)
                                                
                                                For xrow = firstrow To lastrow
                                                            If source_sheet.Cells(xrow, order_num_col).Value = "" Then
                                                                        source_sheet.Cells(xrow, order_num_col).Value = source_sheet.Cells(xrow - 1, order_num_col)
                                                            End If
                                                Next
                                                
                                                For xrow = firstrow To lastrow
                                                            If source_sheet.Cells(xrow, Cust_po_num_col).Value = "" Then
                                                                        source_sheet.Cells(xrow, Cust_po_num_col).Value = source_sheet.Cells(xrow - 1, Cust_po_num_col)
                                                            End If
                                                Next
                                                
                                                If lastrow > firstrow Then
                                                            Source_col1 = Application.Match("Customer PO Number", source_sheet.Rows(firstrow), 0)
                                                            Source_col4 = Application.Match("Ship Date", source_sheet.Rows(firstrow), 0)
                                                            Source_col5 = Application.Match("Ordered Quantity", source_sheet.Rows(firstrow), 0)
                                                            Source_col6 = Application.Match("Order Number", source_sheet.Rows(firstrow), 0)
                                                            Source_col7 = Application.Match("Ordered Item", source_sheet.Rows(firstrow), 0)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
                                                            source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
                                                End If
                                    End If
                        End If
                        
                        'code to ensure that the data is being inputted correct
                        batch_audit_Col = Application.Match("BATCH1", Dash.Rows("1:1"), 0)
                        batch_audit_Col_end = Application.Match("X", Dash.Columns(batch_audit_Col - 1), 0)
                        Dash.Range(Dash.Cells(3, batch_audit_Col), Dash.Cells(batch_audit_Col_end - 1, batch_audit_Col)).ClearContents
                        
                        'locate the last row of the file in question
                        lastrow = source_sheet.UsedRange.Rows.Count
                        
                        validation_counter = 0
                        
                        For x = 1 To 50
                                    'generate a random variable between the first and last row in the file
                                    randgen_value = WorksheetFunction.RandBetween(firstrow + 1, lastrow)
                                    
                                    'select random serial # from the list using the random variable as the column
                                    rand_Value_T1 = source_sheet.Cells(randgen_value, Source_col10).Value
                                    
                                    'select random part # from the list using the random variable as the column
                                    rand_Value_T2 = source_sheet.Cells(randgen_value, Source_col7).Value
                        
                                    'from whichever variable is selected, pull back the customer PO
                                    If Source_col10 <> "" Then
                                                'sets of the customer PO match from the source file
                                                Set rand_match = .Range(.Cells(firstrow, Source_col10), .Cells(lastrow, Source_col10))
                                                Set rand_index = .Range(.Cells(firstrow, Source_col1), .Cells(lastrow, Source_col10))
                                                
                                                'from whichever variable is selected, pull back the customer PO
                                                source_PO = Application.Index(rand_index, Application.Match(rand_Value_T1, rand_match, 0), Source_col1)
                                                
                                                'sets up index match parameters for the hopper file
                                                Set rand_match = .Range(.Cells(firstrow, Target_col1), .Cells(randgen_lastrow, Target_col1))
                                                Set rand_index = .Range(.Cells(firstrow, Target_col1), .Cells(randgen_lastrow, Target_col10))
                                                
                                                'sets uses the customer PO previously retrieved to pull back the SN
                                                hopper_sn = Application.Index(rand_index, Application.Match(source_PO, rand_match, 0), Target_col10)
                                                
                                                'compares the original source SN with the hopper SN
                                                'if a match, then the validation counter increases by one
                                                'if it gets to 5 then
                                                last_batch_row = Dash.Columns(batch_audit_Col).Find("*", , , , xlRows, xlPrevious).Row + 1
                                                If rand_Value_T1 = hopper_sn Then
                                                            'if a mismatch doe snot occur, add a(0) in the correct batch column
                                                            Dash.Cells(last_batch_row, batch_audit_Col).Value = 0
                                                            validation_counter = validation_counter + 1
                                                            If validation_counter = 5 Then
                                                                        Exit For
                                                            End If
                                                            
                                                ElseIf rand_Value <> hopper_sn Then
                                                            Dash.Cells(last_batch_row, batch_audit_Col).Value = 1
                                                            Exit For
                                                
                                                End If
                                    End If
                                    
                                    'what to do if the validation cannot be made in 50 attempts
                                    If x = 50 And validation_counter < 5 Then
                                                 'if an mismatch occurs, add an error marker(1) in the correct batch column
                                                Dash.Cells(last_batch_row, batch_audit_Col).Value = 1
                                                Exit For
                                    End If
                                
                                
                                
                                ElseIf Source_col7 <> "" Then
                                                'sets of the customer PO match from the source file
                                                Set rand_match = .Range(.Cells(firstrow, Source_col7), .Cells(lastrow, Source_col7))
                                                Set rand_index = .Range(.Cells(firstrow, Source_col1), .Cells(lastrow, Source_col7))
                                                
                                                'from whichever variable is selected, pull back the customer PO
                                                source_PO = Application.Index(rand_index, Application.Match(rand_Value_T2, rand_match, 0), Source_col1)
                                                
                                                'sets up index match parameters for the hopper file
                                                Set rand_match = .Range(.Cells(firstrow, Target_col1), .Cells(randgen_lastrow, Target_col1))
                                                Set rand_index = .Range(.Cells(firstrow, Target_col1), .Cells(randgen_lastrow, Target_col7))
                                                
                                                'sets uses the customer PO previously retrieved to pull back the SN
                                                hopper_part = Application.Index(rand_index, Application.Match(source_PO, rand_match, 0), Target_col7)
                                            
                                                'compares the original source SN with the hopper SN
                                                'if a match, then the validation counter increases by one
                                                'if it gets to 5 then
                                                last_ batch_row = Dash.Columns(batch_audit_Col).Find("*", , , , xlRows, xlPrevious).Row + 1
                                                If rand_Value_T2 = hopper_part Then
                                                            'if a mismatch doe snot occur, add a(0) in the correct batch column
                                                            Dash.Cells(last_batch_row, batch_audit_Col).Value = 0
                                                            validation_counter = validation_counter + 1
                                                            If validation_counter = 5 Then
                                                                        Exit For
                                                            End If
                                                            
                                                ElseIf rand_Value_T2 = hopper_part Then
                                                             'if an mismatch occurs, add an error marker(1) in the correct batch column
                                                            Dash.Cells(last_batch_row, batch_audit_Col).Value = 1
                                                            Exit For
                                                
                                                End If
                                    End If
                                    
                                    'what to do if the validation cannot be made in 50 attempts
                                    If x = 50 And validation_counter < 5 Then
                                                Dash.Cells(last_batch_row, batch_audit_Col).Value = 1
                                                Exit For
                                    End If
                            
                                
                                End If
                        Next
                    
                        'Add additional process to ensure that the data to the hopper is added to the bottom of the last used row
                        'and not simply to the last customer PO row.  This way even if there is a missing PO or something,
                        'the data will be okay.


                        If IsFileOpen(folderpath & strFileName) Then
                                    Workbooks(strFileName).Close False
                                    fso.MoveFile folderpath & strFileName, destinationpath & strFileName
                        End If
                        
                        strFileName = Dir
            Loop
        
            'puts a date stamp on the dashboard for the respective batch run
            Dash.Range("D2").Value = Format(Date, "mm/dd/yyyy")
            
            'saves and closes the hopper
            Workbooks(hoppername).Save
            Workbooks(hoppername).Close True
            Calculate
            'keeps track of the errors on the dashboard and presents the user with a count of errors.
            error_count = WorksheetFunction.CountIf(Dash.Range("I3:I100"), 1)
            If Dash.Range("F2").Value = "Batch Failed" Then
                        MsgBox "Batch Failed.  Contact Analyst"
            ElseIf error_count > 0 Then
                        MsgBox "Batch Complete with - " & error_count & " errors."
                Else
                        MsgBox "Batch Complete without errors."
            End If


            
            ActiveWorkbook.Save
            
            Exit Sub
            
'########################################################ERROR HANDLING##############################################################
'########################################################ERROR HANDLING##############################################################
'########################################################ERROR HANDLING##############################################################
                        
'everything past here is error handling
nextsheet:
            Batch_col = Application.Match("Reports that had Errors:", Dash.Rows("1:1"), 0)
            If IsFileOpen(folderpath & strFileName) Then
                        Workbooks(strFileName).Close False
                        Last_Err_Rpt = Dash.Columns(Batch_col).Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                        Dash.Cells(Last_Err_Rpt, Batch_col).Value = batch_num
                        Dash.Cells(Last_Err_Rpt, Batch_col + 1).Value = strFileName
                        strFileName = Dir
            Else
                        Last_Err_Rpt = Dash.Columns(Batch_col).Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
                        Dash.Cells(Last_Err_Rpt, Batch_col).Value = batch_num
                        Dash.Cells(Last_Err_Rpt, Batch_col + 1).Value = strFileName
                        strFileName = Dir
            End If
            GoTo loopsetter
End With
                                
End Sub[B]
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,100
Office Version
2019, 2016, 2013
Platform
Windows
You DIMs are wrong

Dim lastRowIndex, last_row, x_row, target_last_row, nextrow, lastrow As Long

will only DIM lastrow as Long, the rest will be variable

so some will undebatable be the wrong types and could over use the available resources

Long is 4 bytes, where as a variant is 16 to 22 each, and you have many of those

I would think (unproven) declare each in a Global way so they sit outside of modules and used as necessary
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,687
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
The maximum size for a module is 64KB. 28 pages sounds to me like it would be there or thereabouts, so I strongly suggest you break it up into more modules.
 

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
436
You DIMs are wrong

Dim lastRowIndex, last_row, x_row, target_last_row, nextrow, lastrow As Long

will only DIM lastrow as Long, the rest will be variable

so some will undebatable be the wrong types and could over use the available resources

Long is 4 bytes, where as a variant is 16 to 22 each, and you have many of those

I would think (unproven) declare each in a Global way so they sit outside of modules and used as necessary

Okay this is helpful thanks!

Let me clarify.

You're saying rather than declaring the same variables in the way I have in the previous example, I should EACH of them individually as GLOBAL

for example:

Code:
[COLOR=#574123]Dim Target_col1, Target_col2, Target_col3, Target_col4, Target_col5, Target_col6, Target_col7, Target_col8, Target_col9, Target_col10 As Integer[/COLOR]
becomes:

Code:
[COLOR=#574123]Global Target_col1 as integer
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col2 [/COLOR][COLOR=#574123]as integer[/COLOR][COLOR=#574123]
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col3 [/COLOR][COLOR=#574123]as integer[/COLOR][COLOR=#574123]
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col4 [/COLOR][COLOR=#574123]as integer[/COLOR][COLOR=#574123]
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col5 [/COLOR][COLOR=#574123]as integer[/COLOR][COLOR=#574123]
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col6 [/COLOR][COLOR=#574123]as integer[/COLOR][COLOR=#574123]
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col7 [/COLOR][COLOR=#574123]as integer[/COLOR][COLOR=#574123]
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col8 [/COLOR][COLOR=#574123]as integer[/COLOR][COLOR=#574123]
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col9 [/COLOR][COLOR=#574123]as integer[/COLOR][COLOR=#574123]
[/COLOR][COLOR=#574123]Global [/COLOR][COLOR=#574123]Target_col10 as Integer[/COLOR]
Because this way the code isn't declaring them all except the last one as variant (Which I think is a large space user) and I will only have to declare each of them once. Yes?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,687
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Use Dim not Global.
 

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
436
Use Dim not Global.
Thanks.

Mole999 said to " declare each in a Global way so they sit outside of modules and used as necessary".

The way I know to do this is to declare them "GLOBAL X AS Y" above the subroutine and below the option explicit line.

Is this not the way to do it?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,687
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Variables should have the narrowest possible scope as a general rule, so use as few Public ones as you can. (Global is an older term, superseded by Public)

I think breaking up your modules is more important though.
 

Forum statistics

Threads
1,089,432
Messages
5,408,187
Members
403,188
Latest member
Sanjana Ramesh

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top