A Better Way to Validate Data?

M

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
As I'm writing all of this code, I'm thinking "There's got to be a better way". I'm writing a script that will prompt the user for a source data file, and then review the column headers to make sure the file contains all the appropriate data. If not, it kicks back a message stating which columns are wrong. If all the data checks out then it is copied and pasted (in the same order the headers were evaluated) so I can further manipulate the data from there.

The way I've done this so far is a lengthy code which looks header by header and returns the column number. Then it checks to make sure each column number is valid. If something was missed, it's added to an error message.

What I have now works, but there has to be a cleaner way to code this. This little exercise is something I do with quite a few scripts. Are there any suggestions for improvement?

HTML: 
'Declare Variables
'Begin Script

Set Template = ActiveWorkbook
Set Controls = Template.Sheets("Controls")

Application.DisplayAlerts = False
On Error Resume Next
Template.Sheets("IR ISO Data").Delete
On Error GoTo 0
Application.DisplayAlerts = True

MsgBox "Please select the most recent IR ISO File." & vbNewLine & vbNewLine & "This MUST be currently closed."

'Set up list of file filters

Filt = "Text Files (*.txt), *.txt," & _
        "Lotus Files (*.prn),*.prn," & _
        "Comma Separated Files (*.csv), *.csv," & _
        "Tab Separated Files (*.tsv), *.tsv," & _
        "ASCII Files (*.asc),*.asc," & _
        "Excel Files (*.xl*),*.xl*," & _
        "All Files (*.*), *.*"
        
'Display Excel files by default
FilterIndex = 3

'Set the dialog box caption
Title = "Choose the most recent IR ISO File"

'Get the file name
IRISOFileName = Application.GetOpenFilename(filefilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
    
'Exit if dialog box canceled
If IRISOFileName = False Then
    MsgBox "No file was selected, cancelling the request."
    Controls.Activate
    Range("A1").Select
    Exit Sub
End If

'Open Source1 File
Workbooks.Open Filename:=IRISOFileName
Set IRISOFile = ActiveWorkbook

'Copy over only needed columns in the following order: Job, Type, Assembly, Assembly Name, Unit Number, Class, Quantity, Status, Start Date, Competion Date, Random
'Assumes data to copy is on the first worksheet

On Error Resume Next
RequisitionCreationDateColumn = Application.WorksheetFunction.Match("Requisition_Creation_Date", IRISOFile.Sheets(1).Range("1:1"), 0)
RequisitionNumberColumn = Application.WorksheetFunction.Match("Requisition_Number", IRISOFile.Sheets(1).Range("1:1"), 0)
RequisitionLineNumberColumn = Application.WorksheetFunction.Match("Requisition_Line_Number", IRISOFile.Sheets(1).Range("1:1"), 0)
InterfaceSourceCodeColumn = Application.WorksheetFunction.Match("Interface_Source_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
AUTHORIZATIONSTATUSColumn = Application.WorksheetFunction.Match("AUTHORIZATION_STATUS", IRISOFile.Sheets(1).Range("1:1"), 0)
RequisitionerColumn = Application.WorksheetFunction.Match("Requisitioner", IRISOFile.Sheets(1).Range("1:1"), 0)
RequisitionCreatoremailColumn = Application.WorksheetFunction.Match("Requisition_Creator_email", IRISOFile.Sheets(1).Range("1:1"), 0)
IRQuantityColumn = Application.WorksheetFunction.Match("IR_Quantity", IRISOFile.Sheets(1).Range("1:1"), 0)
UnitofMeasureColumn = Application.WorksheetFunction.Match("Unit_of_Measure", IRISOFile.Sheets(1).Range("1:1"), 0)
IRDeliveredQuantityColumn = Application.WorksheetFunction.Match("IR_Delivered_Quantity", IRISOFile.Sheets(1).Range("1:1"), 0)
IRReceivedQuantityColumn = Application.WorksheetFunction.Match("IR_Received_Quantity", IRISOFile.Sheets(1).Range("1:1"), 0)
IROpenQuantityColumn = Application.WorksheetFunction.Match("IR_Open_Quantity", IRISOFile.Sheets(1).Range("1:1"), 0)
IRNeedByDateColumn = Application.WorksheetFunction.Match("IR_Need_By_Date", IRISOFile.Sheets(1).Range("1:1"), 0)
ISOCreationDateColumn = Application.WorksheetFunction.Match("ISO_Creation_Date", IRISOFile.Sheets(1).Range("1:1"), 0)
ISONumberColumn = Application.WorksheetFunction.Match("ISO_Number", IRISOFile.Sheets(1).Range("1:1"), 0)
ISOLineColumn = Application.WorksheetFunction.Match("ISO_Line", IRISOFile.Sheets(1).Range("1:1"), 0)
FlowStatusCodeColumn = Application.WorksheetFunction.Match("Flow_Status_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
FromOrgNameColumn = Application.WorksheetFunction.Match("From_Org_Name", IRISOFile.Sheets(1).Range("1:1"), 0)
FromOrgCodeColumn = Application.WorksheetFunction.Match("From_Org_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
FromOrgTypeColumn = Application.WorksheetFunction.Match("From_Org_Type", IRISOFile.Sheets(1).Range("1:1"), 0)
FromOrgRegionColumn = Application.WorksheetFunction.Match("From_Org_Region", IRISOFile.Sheets(1).Range("1:1"), 0)
FromCountryCodeColumn = Application.WorksheetFunction.Match("From_Country_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
FromCityCodeColumn = Application.WorksheetFunction.Match("From_City_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
ToOrgNameColumn = Application.WorksheetFunction.Match("To_Org_Name", IRISOFile.Sheets(1).Range("1:1"), 0)
ToOrgCodeColumn = Application.WorksheetFunction.Match("To_Org_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
ToOrgTypeColumn = Application.WorksheetFunction.Match("To_Org_Type", IRISOFile.Sheets(1).Range("1:1"), 0)
ToOrgRegionColumn = Application.WorksheetFunction.Match("To_Org_Region", IRISOFile.Sheets(1).Range("1:1"), 0)
ToCountryCodeColumn = Application.WorksheetFunction.Match("To_Country_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
ToCityCodeColumn = Application.WorksheetFunction.Match("To_City_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
ToPersonColumn = Application.WorksheetFunction.Match("To_Person", IRISOFile.Sheets(1).Range("1:1"), 0)
ToPersonEmailColumn = Application.WorksheetFunction.Match("To_Person_Email", IRISOFile.Sheets(1).Range("1:1"), 0)
ItemNumberColumn = Application.WorksheetFunction.Match("Item_Number", IRISOFile.Sheets(1).Range("1:1"), 0)
ItemNumberLengthColumn = Application.WorksheetFunction.Match("Item_Number_Length", IRISOFile.Sheets(1).Range("1:1"), 0)
ItemDescriptionColumn = Application.WorksheetFunction.Match("Item_Description", IRISOFile.Sheets(1).Range("1:1"), 0)
ItemStatusColumn = Application.WorksheetFunction.Match("Item_Status", IRISOFile.Sheets(1).Range("1:1"), 0)
GlobalSupplyStrategyColumn = Application.WorksheetFunction.Match("Global_Supply_Strategy", IRISOFile.Sheets(1).Range("1:1"), 0)
ItemCategoryColumn = Application.WorksheetFunction.Match("Item_Category", IRISOFile.Sheets(1).Range("1:1"), 0)
PlannerCodeColumn = Application.WorksheetFunction.Match("Planner_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
OMOriginalPromiseDateColumn = Application.WorksheetFunction.Match("OM_Original_Promise_Date", IRISOFile.Sheets(1).Range("1:1"), 0)
OMLinePromiseDateColumn = Application.WorksheetFunction.Match("OM_Line_Promise_Date", IRISOFile.Sheets(1).Range("1:1"), 0)
OMScheduledShipDateColumn = Application.WorksheetFunction.Match("OM_Scheduled_Ship_Date", IRISOFile.Sheets(1).Range("1:1"), 0)
ActualShipDateColumn = Application.WorksheetFunction.Match("Actual_Ship_Date", IRISOFile.Sheets(1).Range("1:1"), 0)
ShipMethodCodeColumn = Application.WorksheetFunction.Match("Ship_Method_Code", IRISOFile.Sheets(1).Range("1:1"), 0)
DeliveryNumberColumn = Application.WorksheetFunction.Match("Delivery_Number", IRISOFile.Sheets(1).Range("1:1"), 0)
ISOOrderedQtyColumn = Application.WorksheetFunction.Match("ISO_Ordered_Qty", IRISOFile.Sheets(1).Range("1:1"), 0)
OMUnitofMeasureColumn = Application.WorksheetFunction.Match("OM_Unit_of_Measure", IRISOFile.Sheets(1).Range("1:1"), 0)
ISOShippedQtyColumn = Application.WorksheetFunction.Match("ISO_Shipped_Qty", IRISOFile.Sheets(1).Range("1:1"), 0)
ShippingUnitofMeasureColumn = Application.WorksheetFunction.Match("Shipping_Unit_of_Measure", IRISOFile.Sheets(1).Range("1:1"), 0)
ScheduleArrivalDateColumn = Application.WorksheetFunction.Match("Schedule_Arrival_Date", IRISOFile.Sheets(1).Range("1:1"), 0)
RequisitionCurrencyTransColumn = Application.WorksheetFunction.Match("Requisition_Currency__Trans_", IRISOFile.Sheets(1).Range("1:1"), 0)
OMLineAmountTransColumn = Application.WorksheetFunction.Match("OM_Line_Amount__Trans_", IRISOFile.Sheets(1).Range("1:1"), 0)
LineUnitAmtUSDCorporateColumn = Application.WorksheetFunction.Match("Line_Unit_Amt__USD_Corporate_", IRISOFile.Sheets(1).Range("1:1"), 0)
LineAmountUSDCorporateColumn = Application.WorksheetFunction.Match("Line_Amount__USD_Corporate_", IRISOFile.Sheets(1).Range("1:1"), 0)
IRCurrencyColumn = Application.WorksheetFunction.Match("IR_Currency", IRISOFile.Sheets(1).Range("1:1"), 0)
IRLineAmountTransColumn = Application.WorksheetFunction.Match("IR_Line_Amount__Trans_", IRISOFile.Sheets(1).Range("1:1"), 0)
IRUnitPriceUSDCorporateColumn = Application.WorksheetFunction.Match("IR_Unit_Price__USD_Corporate_", IRISOFile.Sheets(1).Range("1:1"), 0)
IRLineAmountUSDCorporateColumn = Application.WorksheetFunction.Match("IR_Line_Amount__USD_Corporate_", IRISOFile.Sheets(1).Range("1:1"), 0)
On Error GoTo 0

If Not RequisitionCreationDateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "RequisitionCreationDate"
Else
End If

If Not RequisitionNumberColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "RequisitionNumber"
Else
End If

If Not RequisitionLineNumberColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "RequisitionLineNumber"
Else
End If

If Not InterfaceSourceCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "InterfaceSourceCode"
Else
End If

If Not AUTHORIZATIONSTATUSColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "AUTHORIZATIONSTATUS"
Else
End If

If Not RequisitionerColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "Requisitioner"
Else
End If

If Not RequisitionCreatoremailColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "RequisitionCreatoremail"
Else
End If

If Not IRQuantityColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IRQuantity"
Else
End If

If Not UnitofMeasureColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "UnitofMeasure"
Else
End If

If Not IRDeliveredQuantityColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IRDeliveredQuantity"
Else
End If

If Not IRReceivedQuantityColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IRReceivedQuantity"
Else
End If

If Not IROpenQuantityColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IROpenQuantity"
Else
End If

If Not IRNeedByDateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IRNeedByDate"
Else
End If

If Not ISOCreationDateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ISOCreationDate"
Else
End If

If Not ISONumberColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ISONumber"
Else
End If

If Not ISOLineColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ISOLine"
Else
End If

If Not FlowStatusCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "FlowStatusCode"
Else
End If

If Not FromOrgNameColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "FromOrgName"
Else
End If

If Not FromOrgCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "FromOrgCode"
Else
End If

If Not FromOrgTypeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "FromOrgType"
Else
End If

If Not FromOrgRegionColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "FromOrgRegion"
Else
End If

If Not FromCountryCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "FromCountryCode"
Else
End If

If Not FromCityCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "FromCityCode"
Else
End If

If Not ToOrgNameColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ToOrgName"
Else
End If

If Not ToOrgCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ToOrgCode"
Else
End If

If Not ToOrgTypeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ToOrgType"
Else
End If

If Not ToOrgRegionColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ToOrgRegion"
Else
End If

If Not ToCountryCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ToCountryCode"
Else
End If

If Not ToCityCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ToCityCode"
Else
End If

If Not ToPersonColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ToPerson"
Else
End If

If Not ToPersonEmailColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ToPersonEmail"
Else
End If

If Not ItemNumberColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ItemNumber"
Else
End If

If Not ItemNumberLengthColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ItemNumberLength"
Else
End If

If Not ItemDescriptionColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ItemDescription"
Else
End If

If Not ItemStatusColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ItemStatus"
Else
End If

If Not GlobalSupplyStrategyColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "GlobalSupplyStrategy"
Else
End If

If Not ItemCategoryColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ItemCategory"
Else
End If

If Not PlannerCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "PlannerCode"
Else
End If

If Not OMOriginalPromiseDateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "OMOriginalPromiseDate"
Else
End If

If Not OMLinePromiseDateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "OMLinePromiseDate"
Else
End If

If Not OMScheduledShipDateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "OMScheduledShipDate"
Else
End If

If Not ActualShipDateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ActualShipDate"
Else
End If

If Not ShipMethodCodeColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ShipMethodCode"
Else
End If

If Not DeliveryNumberColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "DeliveryNumber"
Else
End If

If Not ISOOrderedQtyColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ISOOrderedQty"
Else
End If

If Not OMUnitofMeasureColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "UnitofMeasure"
Else
End If

If Not ISOShippedQtyColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ISOShippedQty"
Else
End If

If Not ShippingUnitofMeasureColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "UnitofMeasure"
Else
End If

If Not ScheduleArrivalDateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "ScheduleArrivalDate"
Else
End If

If Not RequisitionCurrencyTransColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "RequisitionCurrencyTrans"
Else
End If

If Not OMLineAmountTransColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "OMLineAmountTrans"
Else
End If

If Not LineUnitAmtUSDCorporateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "LineUnitAmtUSDCorporate"
Else
End If

If Not LineAmountUSDCorporateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "LineAmountUSDCorporate"
Else
End If

If Not IRCurrencyColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IRCurrency"
Else
End If

If Not IRLineAmountTransColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IRLineAmountTrans"
Else
End If

If Not IRUnitPriceUSDCorporateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IRUnitPriceUSDCorporate"
Else
End If

If Not IRLineAmountUSDCorporateColumn > 0 Then
    ErrorMessage = ErrorMessage & vbNewLine & "IRLineAmountUSDCorporate"
Else
End If


'Message to state which fields are missing from the report

If Not ErrorMessage = "" Then
    MsgBox "Looks like you're missing some data.  The source data you provided is missing the following:" _
    & vbNewLine & ErrorMessage & vbNewLine & vbNewLine & "Please re-run the IR ISO report and try again." _
    & vbNewLine & "Cancelling the process."
    
    Application.DisplayAlerts = False
    IRISOFile.Close
    Application.DisplayAlerts = True
    Controls.Activate
    Controls.Range("A1").Select
    
    Exit Sub
    
Else
End If

'Add a new worksheet for Work Orders
Template.Sheets.Add(After:=Template.Sheets(Template.Sheets.Count)).Name = "IR ISO Data"
Set IRISOData = Template.Sheets("IR ISO Data")

'Copy over only needed columns
IRISOFile.Sheets(1).Columns(RequisitionCreationDateColumn).Copy IRISOData.Columns(1)
IRISOFile.Sheets(1).Columns(RequisitionNumberColumn).Copy IRISOData.Columns(2)
IRISOFile.Sheets(1).Columns(RequisitionLineNumberColumn).Copy IRISOData.Columns(3)
IRISOFile.Sheets(1).Columns(InterfaceSourceCodeColumn).Copy IRISOData.Columns(4)
IRISOFile.Sheets(1).Columns(AUTHORIZATIONSTATUSColumn).Copy IRISOData.Columns(5)
IRISOFile.Sheets(1).Columns(RequisitionerColumn).Copy IRISOData.Columns(6)
IRISOFile.Sheets(1).Columns(RequisitionCreatoremailColumn).Copy IRISOData.Columns(7)
IRISOFile.Sheets(1).Columns(IRQuantityColumn).Copy IRISOData.Columns(8)
IRISOFile.Sheets(1).Columns(UnitofMeasureColumn).Copy IRISOData.Columns(9)
IRISOFile.Sheets(1).Columns(IRDeliveredQuantityColumn).Copy IRISOData.Columns(10)
IRISOFile.Sheets(1).Columns(IRReceivedQuantityColumn).Copy IRISOData.Columns(11)
IRISOFile.Sheets(1).Columns(IROpenQuantityColumn).Copy IRISOData.Columns(12)
IRISOFile.Sheets(1).Columns(IRNeedByDateColumn).Copy IRISOData.Columns(13)
IRISOFile.Sheets(1).Columns(ISOCreationDateColumn).Copy IRISOData.Columns(14)
IRISOFile.Sheets(1).Columns(ISONumberColumn).Copy IRISOData.Columns(15)
IRISOFile.Sheets(1).Columns(ISOLineColumn).Copy IRISOData.Columns(16)
IRISOFile.Sheets(1).Columns(FlowStatusCodeColumn).Copy IRISOData.Columns(17)
IRISOFile.Sheets(1).Columns(FromOrgNameColumn).Copy IRISOData.Columns(18)
IRISOFile.Sheets(1).Columns(FromOrgCodeColumn).Copy IRISOData.Columns(19)
IRISOFile.Sheets(1).Columns(FromOrgTypeColumn).Copy IRISOData.Columns(20)
IRISOFile.Sheets(1).Columns(FromOrgRegionColumn).Copy IRISOData.Columns(21)
IRISOFile.Sheets(1).Columns(FromCountryCodeColumn).Copy IRISOData.Columns(22)
IRISOFile.Sheets(1).Columns(FromCityCodeColumn).Copy IRISOData.Columns(23)
IRISOFile.Sheets(1).Columns(ToOrgNameColumn).Copy IRISOData.Columns(24)
IRISOFile.Sheets(1).Columns(ToOrgCodeColumn).Copy IRISOData.Columns(25)
IRISOFile.Sheets(1).Columns(ToOrgTypeColumn).Copy IRISOData.Columns(26)
IRISOFile.Sheets(1).Columns(ToOrgRegionColumn).Copy IRISOData.Columns(27)
IRISOFile.Sheets(1).Columns(ToCountryCodeColumn).Copy IRISOData.Columns(28)
IRISOFile.Sheets(1).Columns(ToCityCodeColumn).Copy IRISOData.Columns(29)
IRISOFile.Sheets(1).Columns(ToPersonColumn).Copy IRISOData.Columns(30)
IRISOFile.Sheets(1).Columns(ToPersonEmailColumn).Copy IRISOData.Columns(31)
IRISOFile.Sheets(1).Columns(ItemNumberColumn).Copy IRISOData.Columns(32)
IRISOFile.Sheets(1).Columns(ItemNumberLengthColumn).Copy IRISOData.Columns(33)
IRISOFile.Sheets(1).Columns(ItemDescriptionColumn).Copy IRISOData.Columns(34)
IRISOFile.Sheets(1).Columns(ItemStatusColumn).Copy IRISOData.Columns(35)
IRISOFile.Sheets(1).Columns(GlobalSupplyStrategyColumn).Copy IRISOData.Columns(36)
IRISOFile.Sheets(1).Columns(ItemCategoryColumn).Copy IRISOData.Columns(37)
IRISOFile.Sheets(1).Columns(PlannerCodeColumn).Copy IRISOData.Columns(38)
IRISOFile.Sheets(1).Columns(OMOriginalPromiseDateColumn).Copy IRISOData.Columns(39)
IRISOFile.Sheets(1).Columns(OMLinePromiseDateColumn).Copy IRISOData.Columns(40)
IRISOFile.Sheets(1).Columns(OMScheduledShipDateColumn).Copy IRISOData.Columns(41)
IRISOFile.Sheets(1).Columns(ActualShipDateColumn).Copy IRISOData.Columns(42)
IRISOFile.Sheets(1).Columns(ShipMethodCodeColumn).Copy IRISOData.Columns(43)
IRISOFile.Sheets(1).Columns(DeliveryNumberColumn).Copy IRISOData.Columns(44)
IRISOFile.Sheets(1).Columns(ISOOrderedQtyColumn).Copy IRISOData.Columns(45)
IRISOFile.Sheets(1).Columns(OMUnitofMeasureColumn).Copy IRISOData.Columns(46)
IRISOFile.Sheets(1).Columns(ISOShippedQtyColumn).Copy IRISOData.Columns(47)
IRISOFile.Sheets(1).Columns(ShippingUnitofMeasureColumn).Copy IRISOData.Columns(48)
IRISOFile.Sheets(1).Columns(ScheduleArrivalDateColumn).Copy IRISOData.Columns(49)
IRISOFile.Sheets(1).Columns(RequisitionCurrencyTransColumn).Copy IRISOData.Columns(50)
IRISOFile.Sheets(1).Columns(OMLineAmountTransColumn).Copy IRISOData.Columns(51)
IRISOFile.Sheets(1).Columns(LineUnitAmtUSDCorporateColumn).Copy IRISOData.Columns(52)
IRISOFile.Sheets(1).Columns(LineAmountUSDCorporateColumn).Copy IRISOData.Columns(53)
IRISOFile.Sheets(1).Columns(IRCurrencyColumn).Copy IRISOData.Columns(54)
IRISOFile.Sheets(1).Columns(IRLineAmountTransColumn).Copy IRISOData.Columns(55)
IRISOFile.Sheets(1).Columns(IRUnitPriceUSDCorporateColumn).Copy IRISOData.Columns(56)
IRISOFile.Sheets(1).Columns(IRLineAmountUSDCorporateColumn).Copy IRISOData.Columns(57)

IRISOData.Activate
ActiveWindow.FreezePanes = False
With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
End With
ActiveWindow.FreezePanes = True

Application.DisplayAlerts = False
IRISOFile.Close False
Application.DisplayAlerts = True

Controls.Activate
Controls.Range("A1").Select

MsgBox "New IR ISO Data have been loaded."

End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Try something like this (not tested)...

Code: 
    [COLOR=green]'Declare Variables[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Template [COLOR=darkblue]As[/COLOR] Workbook, Controls [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] IRISOData [COLOR=darkblue]As[/COLOR] Worksheet, IRISOFile [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] arrHeaders [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], arrHeaderCols [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ErrorMessage [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], Filt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], FilterIndex [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Title [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], IRISOFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=green]'Begin Script[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] Template = ActiveWorkbook
    [COLOR=darkblue]Set[/COLOR] Controls = Template.Sheets("Controls")
    
    Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    Template.Sheets("IR ISO Data").Delete
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
    
    MsgBox "Please select the most recent IR ISO File." & vbNewLine & vbNewLine & "This MUST be currently closed."
    
    [COLOR=green]'Set up list of file filters[/COLOR]
    
    Filt = "Text Files (*.txt), *.txt," & _
           "Lotus Files (*.prn),*.prn," & _
           "Comma Separated Files (*.csv), *.csv," & _
           "Tab Separated Files (*.tsv), *.tsv," & _
           "ASCII Files (*.asc),*.asc," & _
           "Excel Files (*.xl*),*.xl*," & _
           "All Files (*.*), *.*"
    
    [COLOR=green]'Display Excel files by default[/COLOR]
    FilterIndex = 3
    
    [COLOR=green]'Set the dialog box caption[/COLOR]
    Title = "Choose the most recent IR ISO File"
    
    [COLOR=green]'Get the file name[/COLOR]
    IRISOFileName = Application.GetOpenFilename(filefilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)
    
    [COLOR=green]'Exit if dialog box canceled[/COLOR]
    [COLOR=darkblue]If[/COLOR] IRISOFileName = [COLOR=darkblue]False[/COLOR] [COLOR=darkblue]Then[/COLOR]
        MsgBox "No file was selected, cancelling the request."
        Controls.Activate
        Range("A1").Select
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'Open Source1 File[/COLOR]
    Workbooks.Open Filename:=IRISOFileName
    [COLOR=darkblue]Set[/COLOR] IRISOFile = ActiveWorkbook
    
    [COLOR=green]'Copy over only needed columns in the following order: Job, Type, Assembly, Assembly Name, Unit Number, Class, Quantity, Status, Start Date, Competion Date, Random[/COLOR]
    [COLOR=green]'Assumes data to copy is on the first worksheet[/COLOR]
    
    arrHeaders = Split("Requisition_Creation_Date,Requisition_Number,Requisition_Line_Number," & _
                   "Interface_Source_Code,AUTHORIZATION_STATUS,Requisitioner," & _
                   "Requisition_Creator_email,IR_Quantity,Unit_of_Measure," & _
                   "IR_Delivered_Quantity,IR_Received_Quantity,IR_Open_Quantity," & _
                   "IR_Need_By_Date,ISO_Creation_Date,ISO_Number,ISO_Line,Flow_Status_Code," & _
                   "From_Org_Name,From_Org_Code,From_Org_Type,From_Org_Region," & _
                   "From_Country_Code,From_City_Code,To_Org_Name,To_Org_Code,To_Org_Type," & _
                   "[COLOR=darkblue]To[/COLOR]_Org_Region,[COLOR=darkblue]To[/COLOR]_Country_Code,To_City_Code,[COLOR=darkblue]To[/COLOR]_Person,To_Person_Email," & _
                   "Item_Number,Item_Number_Length,Item_Description,Item_Status," & _
                   "Global_Supply_Strategy,Item_Category,Planner_Code,OM_Original_Promise_Date," & _
                   "OM_Line_Promise_Date,OM_Scheduled_Ship_Date,Actual_Ship_Date," & _
                   "Ship_Method_Code,Delivery_Number,ISO_Ordered_Qty,OM_Unit_of_Measure," & _
                   "ISO_Shipped_Qty,Shipping_Unit_of_Measure,Schedule_Arrival_Date," & _
                   "Requisition_Currency__Trans_,OM_Line_Amount__Trans_," & _
                   "Line_Unit_Amt__USD_Corporate_,Line_Amount__USD_Corporate_,IR_Currency," & _
                   "IR_Line_Amount__Trans_,IR_Unit_Price__USD_Corporate_,IR_Line_Amount__USD_Corporate_", ",")
    
                        
    [COLOR=darkblue]ReDim[/COLOR] arrHeaderCols(0 To [COLOR=darkblue]UBound[/COLOR](arrHeaders))
                        
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
        [COLOR=darkblue]For[/COLOR] i = 0 To [COLOR=darkblue]UBound[/COLOR](arrHeaders)
            arrHeaderCols(i) = Application.WorksheetFunction.Match(arrHeaders(i), IRISOFile.Sheets(1).Range("1:1"), 0)
            [COLOR=darkblue]If[/COLOR] arrHeaderCols(i) = [COLOR=darkblue]Empty[/COLOR] [COLOR=darkblue]Then[/COLOR] ErrorMessage = [COLOR=darkblue]Error[/COLOR]Message & vbNewLine & arrHeaders(i)
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]On[/COLOR] Error [COLOR=darkblue]GoTo[/COLOR] 0
    
    [COLOR=green]'Message to state which fields are missing from the report[/COLOR]
    [COLOR=darkblue]If[/COLOR] ErrorMessage <> "" [COLOR=darkblue]Then[/COLOR]
        MsgBox "Looks like you're missing some data.  The source data you provided is missing the following:" _
        & vbNewLine & ErrorMessage & vbNewLine & vbNewLine & "Please re-run the IR ISO report and try again." _
        & vbNewLine & "Cancelling the process."
        
        Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
        IRISOFile.Close
        Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
        Controls.Activate
        Controls.Range("A1").Select
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'Add a new worksheet for Work Orders[/COLOR]
    Template.Sheets.Add(After:=Template.Sheets(Template.Sheets.Count)).Name = "IR ISO Data"
    [COLOR=darkblue]Set[/COLOR] IRISOData = Template.Sheets("IR ISO Data")
    
    [COLOR=green]'Copy over only needed columns[/COLOR]
    [COLOR=darkblue]For[/COLOR] i = 0 To [COLOR=darkblue]UBound[/COLOR](arrHeaderCols)
        IRISOFile.Sheets(1).Columns(arrHeaderCols(i)).Copy IRISOData.Columns(i + 1)
    [COLOR=darkblue]Next[/COLOR] i
    
    
    IRISOData.Activate
    ActiveWindow.FreezePanes = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]With[/COLOR] ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    ActiveWindow.FreezePanes = [COLOR=darkblue]True[/COLOR]
    
    Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
    IRISOFile.Close [COLOR=darkblue]False[/COLOR]
    Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
    
    Controls.Activate
    Controls.Range("A1").Select
    
    MsgBox "New IR ISO Data have been loaded."
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
I just realized I never said thank you - that was EXACTLY what I needed. I have since used this same logic in a few different areas and it's saved me quite a bit of time. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
For Next loop skipping blank cell, but indexes past next cell
Replies
7
Views
206
NoSparks
N
I
  • Question
Advice for if cell has a value then
Replies
7
Views
273
6StringJazzer
6StringJazzer
I
  • Solved
Flexible code for changing sheet name
Replies
3
Views
283
ipbr21054
I
I
  • Question
Inoice number not being saved in cell
Replies
6
Views
301
ipbr21054
I
I
  • Question
Look for value in column A then check if cell has a value
Replies
4
Views
325
ipbr21054
I

Forum statistics

Threads
1,215,022
Messages
6,122,716
Members
449,093
Latest member
Mnur

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