I get a compile error
"Next without For"
This is the code that i used
Sub consolidatesheets()
Dim I As Long
For I = Sheets.Count To 3
With Sheets(I)
ActiveSheet.Shapes.SelectAll
Selection.Delete
Cells.Select
Range("Z999").Activate
Selection.Hyperlinks.Delete
Range("B2:H2").Select
Selection.Cut
Range("C2").Select
ActiveSheet.Paste
Columns("C:C").ColumnWidth = 40
Columns("F:G").Select
Selection.ColumnWidth = 15
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Columns("H:J").EntireColumn.AutoFit
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Range("C4").Select
Selection.ClearContents
Range("C6").Select
Selection.ClearContents
Range("C8").Select
Selection.ClearContents
Range("C10").Select
Selection.ClearContents
Range("C12").Select
Selection.ClearContents
Range("C14").Select
Selection.ClearContents
Range("C16").Select
Selection.ClearContents
Range("C18").Select
Selection.ClearContents
Range("C20").Select
Selection.ClearContents
Range("C22").Select
Selection.ClearContents
Range("C24").Select
Selection.ClearContents
Range("C26").Select
Selection.ClearContents
Range("C28").Select
Selection.ClearContents
Range("C30").Select
Selection.ClearContents
Range("C3:C4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C5:C6").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C7:C8").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C9:C10").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C11:C12").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C13:C14").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C15:C16").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C17:C18").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C19:C20").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C21:C22").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C23:C24").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C25:C26").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C27:C28").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("C29:C30").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D3:D4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D5:D6").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D7:D8").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D9:D10").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D11:D12").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D13:D14").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D15:D16").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D17:D18").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D19:D20").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D21:D22").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D23:D24").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D25:D26").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D27:D28").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("D29:D30").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Selection.Merge
Range("E3:F3").Select
Selection.ClearContents
Range("E5:F5").Select
Selection.ClearContents
Range("E7:F7").Select
Selection.ClearContents
Range("E9:F9").Select
Selection.ClearContents
Range("E11:F11").Select
Selection.ClearContents
Range("E13:F13").Select
Selection.ClearContents
Range("E15:F15").Select
Selection.ClearContents
Range("E17:F17").Select
Selection.ClearContents
Range("E19:F19").Select
Selection.ClearContents
Range("E21:F21").Select
Selection.ClearContents
Range("E23:F23").Select
Selection.ClearContents
Range("E25:F25").Select
Selection.ClearContents
Range("E27:F27").Select
Selection.ClearContents
Range("E29:F29").Select
Selection.ClearContents
Range("D3:D4").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-1]=""Project Management Cost"",RC[-1]=""Project Management Cost 2012""), ""1"", IF(OR(RC[-1]=""Engineering&pemits Cost"",RC[-1]=""Engineering&Permits Cost 2012""), ""2"", IF(OR(RC[-1]=""Surface works for drilling Cost"",RC[-1]=""Surface works for drilling Cost 2012""), ""3"", IF(OR(RC[-1]=""Drilling services and materials Cost"",RC[-1]=""Drilling services" & _
"erials Cost 2012""),""4"",IF(OR(RC[-1]=""Well Completion Cost"",RC[-1]=""Well Completion Cost 2012""),""5"",IF(OR(RC[-1]=""Surface facility construction Cost"",RC[-1]=""Surface facility construction Cost 2012""),""6"",IF(OR(RC[-1]=""Test production Cost"",RC[-1]=""Test production Cost 2012""),""7"",)))))))"
Range("D3:D4").Select
Selection.AutoFill Destination:=Range("D3:D30"), Type:=xlFillDefault
Range("D3:D30").Select
Next I
End With
End Sub
Bookmarks