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

H

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
464
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
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]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
mole999 said:
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
Click to expand...


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?
 
Upvote 0
RoryA said:
Use Dim not Global.
Click to expand...

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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA Performance / Optimization & Out of Memory Error
Replies
9
Views
2K
Candyman8019
C
G
  • Question Question
Excel VBA Macro steps through ok but when run it causes duplication
Replies
2
Views
4K
Gassy
G
L
  • Question Question
VBA Advanced filter often crashes excel, what causes this? HELP please!
Replies
8
Views
2K
logandiana
L
G
  • Question Question
VBA Windows 10 code crashes excel when executing. Works when stepping through. Runs on Windows 7 fine.
Replies
1
Views
1K
DanteAmor
DanteAmor
M
  • Question Question
Excel crashing - multiple interacting macros
Replies
2
Views
2K
mhwolog
M

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top