[COLOR=#141414][FONT=Verdana]Sub Macro2()[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]'AS SHEET[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Dim Lr As Long[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With Sheets("AS")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("H:H").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("H4:H" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("A5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]With Sheets("AS")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("I:I").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("I4:I" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("B5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With Sheets("AS")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("J:J").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("J4:J" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("C5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With Sheets("AS")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("M:M").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("M4:M" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("F5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With Sheets("AS")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("N:N").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("N4:N" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("G5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]'FL SHEET[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With Sheets("FL")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("H:H").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("H4:H" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("A5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]With Sheets("FL")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("I:I").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("I4:I" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("B5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With Sheets("FL")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("J:J").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("J4:J" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("C5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With Sheets("FL")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("M:M").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("M4:M" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("F5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With Sheets("FL")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Lr = .Range("N:N").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana].Range("N4:N" & Lr).Copy[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Range("G5000").End(xlUp).PasteSpecial xlPasteValues[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.CutCopyMode = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Dim lRow As Integer[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Dim intCol As Long[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Dim rngCell As Range, fn[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Set fn = Application.WorksheetFunction[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]For intCol = 2 To 3[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] For lRow = 353 To 2 Step -1[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] Set rngCell = Cells(lRow, intCol)[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] With rngCell[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] .Value = fn.Substitute(rngCell.Value, Chr(160), Chr(32))[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] .Value = Trim(rngCell.Value)[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] End With[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] If Len(rngCell) = 0 Then[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] rngCell.Delete shift:=xlUp[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] End If[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] Set rngCell = Nothing[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] Next lRow[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Next intCol[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]
[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Sheets("Dump").Select[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]Range("I11").Select[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] Selection.AutoFill Destination:=Range("I11:I206")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] Range("D11").Select[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] Selection.AutoFill Destination:=Range("D11:D206")[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana] Range("A1").Select[/FONT][/COLOR]
[COLOR=#141414][FONT=Verdana]End Sub[/FONT][/COLOR]