how to handle error while looping through sheets

[iamtherforeiam]

Hey Everyone,
I assembled some VBA MAcro I found around the web, especially from this forum to create a program, and thanks to Mr. excel and everyone else for that!
the program extracts daily sale orders from a website(about 200 per day) via weqquery of each URL, puts each one on separate sheet in an excel file, loops thru all sheets and sorts them in a specific order that can than be combined into one sheet and imported into our accounting system.

My issue- when there are errors during the loop of extracting URL's or sorting orders(usually because customer put address in an irregular form) I can't figure out how to tell vba to simply indicate to user which order/sheet was problematic and than continue the looping. I can't just use - 'on error resume next' because I need to know to upload that specific order manually.

below is only part of the entire program 1 SUB WebQueryMultiple(GET URL's) 2 SUB Sortallorders (sort the extrscted orders) 3 SUB CopyDataWithoutHeaders(combines all to one sheet , later to be SUMIF'd and further sorted with another macro) which is pretty long,didnt want to overwhelm the post... I hope this relevant part is enough. please let me know if any more clarification is needed!

Sub WebQueryMultiple()
Dim WSD As Worksheet
Dim WSW As Worksheet
Set WSD = Worksheets("Sheet1")
finalrow = Cells(Rows.Count, 7).End(xlUp).Row

For i = 1 To finalrow
ThisURL = "URL;" & WSD.Cells(i, 7)
Set WSW = Worksheets.Add(After:=Worksheets(Worksheets.Count))
WSW.Select

' Do a web Query
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
ThisURL, Destination:=Range("$A$1"))
'.CommandType = 0
.Name = "Query" & i
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False

.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i


End Sub
Sub Sortallorders()
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then 'Write the sheet name you dont want to apply macro
ws.Activate
Application.Goto Reference:="R1C1" 'write the actual Code to be applied
Cells.Find(What:="Product Code", After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Cut
Application.Goto Reference:="R1C2"
ActiveSheet.Paste
Application.Goto Reference:="R1C11"
ActiveCell.Value = "order #"
Application.Goto Reference:="R1C12"
ActiveCell.Value = "BillCity"
Application.Goto Reference:="R1C13"
ActiveCell.Value = "BillState"
Application.Goto Reference:="R1C14"
ActiveCell.Value = "BillPostal"
Application.Goto Reference:="R1C15"
ActiveCell.Value = "Billing1"
Application.Goto Reference:="R1C16"
ActiveCell.Value = "Billing2"
Application.Goto Reference:="R1C17"
ActiveCell.Value = "ShipCity"
Application.Goto Reference:="R1C18"
ActiveCell.Value = "ShipState"
Application.Goto Reference:="R1C19"
ActiveCell.Value = "ShipPostal"
Application.Goto Reference:="R1C20"
ActiveCell.Value = "Shipping1"
Application.Goto Reference:="R1C21"
ActiveCell.Value = "Shipping2"
Application.Goto Reference:="R1C22"
ActiveCell.Value = "PhoneTemp"
Application.Goto Reference:="R1C23"
ActiveCell.Value = "Phonenumber"
Application.Goto Reference:="R1C24"
ActiveCell.Value = "StandardShip"
Application.Goto Reference:="R1C25"
ActiveCell.Value = "HeavyShip"
Application.Goto Reference:="R1C26"
ActiveCell.Value = "MDtax"
Application.Goto Reference:="R100C27"
ActiveCell.Value = "*EOD*"
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.Goto Reference:="R1C2"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "*EOD*"
Selection.Copy
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Cells.Find(What:="net32-", After:=ActiveCell, LookIn:=xlFormulas, Lookat _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="R2C11"
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Cells.Replace What:="net32-", Replacement:="", Lookat:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="R1C1"
Cells.Find(What:="Billing address:", After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "deletethis"
ActiveCell.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="R2C12"
ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False
Selection.TextToColumns Destination:=Range("L2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat)), TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1).Select
Selection.TextToColumns Destination:=Range("M2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, xlTextFormat), Array(3, xlTextFormat)), TrailingMinusNumbers:=True
Application.AlertBeforeOverwriting = True
Application.DisplayAlerts = True
Application.Goto Reference:="R1C1"
Cells.Find(What:="Billing address:", After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = ""
Application.Goto Reference:="R1C1"
Cells.Find(What:="Billing address:", After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Selection.Cut
Application.Goto Reference:="R2C15"
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Cells.Find(What:="Billing address:", After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Call JoinAndMerge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 0).Select
Selection.Cut
Application.Goto Reference:="R2C16"
ActiveSheet.Paste
ActiveCell.Replace What:="deletethis", Replacement:="", Lookat:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="R1C1"
Cells.Find(What:="Shipping to:", After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "deletethis"
ActiveCell.Offset(-1, 0).Select
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="R2C17"
ActiveSheet.Paste
Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("Q2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat)), TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1).Select
Selection.TextToColumns Destination:=Range("R2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, xlTextFormat), Array(3, xlTextFormat)), TrailingMinusNumbers:=True
Application.AlertBeforeOverwriting = True
Application.DisplayAlerts = True

Application.Goto Reference:="R1C1"
Cells.Find(What:="Shipping to", After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = ""
Application.Goto Reference:="R1C1"
Cells.Find(What:="Shipping to:", After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Selection.Cut
Application.Goto Reference:="R2C20"
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Cells.Find(What:="Shipping to:", After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Call JoinAndMerge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 0).Select
Selection.Cut
Application.Goto Reference:="R2C21"
ActiveSheet.Paste
ActiveCell.Replace What:="deletethis", Replacement:="", Lookat:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="R1C1"
Cells.Find(What:="Phone Number:", After:=ActiveCell, LookIn:=xlFormulas, Lookat _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="R2C22"
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Cells.Replace What:="Phone Number:", Replacement:="", Lookat:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="R2C23"
ActiveCell.FormulaR1C1 = "=PhoneFormat(RC[-1])"
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Cut
Columns("V:V").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Application.Goto Reference:="R1C1"
Cells.Find(What:="Standard shipping:", After:=ActiveCell, LookIn:=xlFormulas, Lookat _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="R2C23"
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Cells.Replace What:="Free", Replacement:="0", Lookat:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Standard shipping:", Replacement:="", Lookat:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="R1C1"
Cells.Find(What:="Heavy shipping surcharge:", After:=ActiveCell, LookIn:=xlFormulas, Lookat _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="R2C24"
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Cells.Replace What:="N/A", Replacement:="0", Lookat:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Heavy shipping surcharge:", Replacement:="", Lookat:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="R2C25"
ActiveCell.FormulaR1C1 = "=IF(RC[-7]=""MD"", ""Tax"", ""Non"")"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("Z:Z").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Application.Goto Reference:="R2C9"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 14)).Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Cells.Find(What:="*EOD*", After:=ActiveCell, LookIn:=xlFormulas, Lookat _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Rows(ActiveCell.Row).Delete
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Columns("E:G").Select
Selection.Delete Shift:=xlToLeft

End If
Next ws

End Sub

''''Sub CopyDataWithoutHeaders()

Sub Combine()
Dim j As Integer
Dim LastRow As Integer
Dim rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Invoices"

'Copy headings
Sheets(4).Activate
Rows("1:1").Select
Selection.Copy
Selection.Copy Destination:=Sheets(1).Range("a1")


'work through sheets
For j = 3 To Sheets.Count ' from sheet 3 to last sheet
Sheets(j).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select




'copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next

End Sub

im new to VBA, im sure lots of things here can be improved and would LOVE any suggestion, but main issue is ERROR handling.
help is much appreciated.