VBA - Issue with F8 Step Thru - Macro just Runs without Stepping?

Johnny Thunder

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. MacOS
Hi Group,


hopefully this is an easy one. When I am stepping thru my code, I get to a line "Workbooks.Open" and as soon as I go to the next step, the code just runs in full to completion without going thru the remaining steps and there is a few left....? Any Ideas why this is happening?

Code: 
Option Explicit
Sub ImportEccsBal1()


Dim nWkb        As Workbook
Dim yr          As String
Dim mnth        As String


yr = Year(Now())
mnth = Range("Month1").Value


Application.DisplayAlerts = False
Application.ScreenUpdating = False
    
'Update Folder Location Here
Workbooks.Open Filename:=("\\Wb\acctg\shared\RPTBUD\BUDGET\Actual\" & yr & "\" & mnth & "\Results\01 Tie Out\06 H8 System Tie Out\BCS H8 System Tie Out.xlsm") '----- After this line code just runs?


Set nWkb = ActiveWorkbook


nWkb.Sheets("Tie Out (H8)").Cells.Copy


With ThisWorkbook.Sheets("BCS").Range("A1")
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Application.CutCopyMode = False
                nWkb.Close SaveChanges:=True
    End With


Calculate


Application.DisplayAlerts = True
Application.ScreenUpdating = True


MsgBox "BCS Tab Updated" & vbNewLine & "File has also updated calculations"


End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Johnny Thunder said:
When I am stepping thru my code, I get to a line "Workbooks.Open" and as soon as I go to the next step, the code just runs in full to completion ..
Click to expand...
This seems to have happened in recent versions of Excel and as far as I know there isn't a 'real solution'. There are a few actions that trigger that behaviour and opening a workbook is just one of them. I have got in the habit of looking through the code for possible triggers and putting a break point on the following line(s) so that the code again stops when it gets to the break point.
 
Upvote 0
@Peter_SSs thanks for the info. I figured it had to be something with Excel since this never happened on older versions of the platform.
 
Upvote 0
IIRC there is a registry setting for that. I'll see if I can locate it.

Edit: it's on Debra's site here.
 
Last edited:
Upvote 0
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: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA code stuck (extract data from worksheet)
Replies
1
Views
411
Alex Blakenburg
A
B
  • Question Question
Macro Works When Stepping Through, Printing First Iteration Blank (Skips Steps without Pauses)
Replies
1
Views
745
Fluff
Fluff
T
  • Question Question
VBA Macro running, but not saving to Sharepoint
Replies
3
Views
771
Fluff
Fluff
Guna13
  • Question Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
1K
Guna13
Guna13
E
  • Question Question
Macro Just Stop Halfway Through
Replies
3
Views
553
smozgur
smozgur

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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