Modifying a Recorded Macro

I recorded this macro and I would like to be able to run it on every worksheet in the workbook.

I tried adding for each workbook and next workbook to the macro I recorded, but it doesen't go to the next worksheet.

Also, at the end of the data is some "stuff" I would like to delete, but somehow doesn't seem to get recorded in my macro. Please see screen capture to see what I mean.


Here is what I have:

**************************************************************************
Sub FormatColoradoData()

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

'START OF RECORDED MACRO

    Range("A5:A7").Select
    Selection.Copy
    Range("J5:J7").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("K5:K7").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("J5:J7").Select
    ActiveCell.FormulaR1C1 = "WELLNAME"
    Range("K5:K7").Select
    ActiveCell.FormulaR1C1 = "WELLNO"
    Range("J8").Select
    ActiveCell.FormulaR1C1 = "=R1C1"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = "=R2C2"
    Range("J8:K8").Select
    Selection.Copy
    Range("A8").Select
    Selection.End(xlDown).Select
    Range("J37:K37").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K8").Select

    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Rows("1:4").Select
    Selection.Delete Shift:=xlUp
    Range("A1:A3").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Rows("34:34").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("E1:G1").Select
    Selection.Delete Shift:=xlUp
    Range("E1:G2").Select
    Range("E3:G3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1:G3").Select
    With Selection
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("H1:H3").Select
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("E1:H3").Select
    Selection.UnMerge
    Range("D1:D3").Select
    Selection.Copy
    Range("E1:E3").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("F1:F3").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("G1:G3").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("F1:F3").Select
    ActiveCell.FormulaR1C1 = "CASING PSI"
    Range("G1:G3").Select
    ActiveCell.FormulaR1C1 = "LINE PSI"
    Range("F4").Select
   
   
   
    Range("G1:G3").Select
    Selection.Copy
    Range("H1:H3").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("I1:I3").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("J:J").EntireColumn.AutoFit
    Columns("K:K").ColumnWidth = 14.14
   
   

    Range("A4").Select
    Selection.End(xlDown).Select
    Rows("34:34").Select
    Range("B34").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A1:A3").Select
   
  Next WS
 
End Sub