Trouble with Cutting and Inserting a Range

[Luke]

I'm trying to reformat a report I have to run a lot, but I keep getting an
error whenever I try to cut a range, then insert the cut cells at another
location. Here's my code:

Sub ReformatEEList()

Dim CutOff As Long

Application.ScreenUpdating = False

Range("C5").Insert Shift:=xlToRight
Range("F5:G" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("D5").Insert(xlShiftToRight)
Range("G5:G" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("F5").Insert(xlShiftToRight)
Range("H:I").Insert
Range("H5").Value = "Department"
Range("I5").Value = "Job Title"
Range("M5:M" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("J5").Insert(xlShiftToRight)
Range("R5:R" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("K5").Insert(xlShiftToRight)
Range("M5:N" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("L5").Insert(xlShiftToRight)
Range("R5:R" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("N5").Insert(xlShiftToRight)
Range("P:R").Delete
Range("J:N").NumberFormat = "m/d/yyyy;@"
Range("P:T").NumberFormat = "m/d/yyyy;@"
Range("P6:T" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _

"=IF(RC[-6]=""00/00/0000"",""00/00/0000"",IF(ISERROR(DATE(YEAR(RC[-6]),MONTH(RC[-6]),DAY(RC[-6]))),RC[-6],DATE(YEAR(RC[-6]),MONTH(RC[-6]),DAY(RC[-6]))))"
Range("P6:T" & ActiveSheet.UsedRange.Rows.Count).Copy
Range("J6").PasteSpecial(Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False)
Range("P:T").Delete
With Range("A5:O" & ActiveSheet.UsedRange.Rows.Count)
.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5"),
Order2:=xlAscending, _
Key3:=Range("B5"), Order3:=xlAscending, Header:=xlYes
End With
CutOff = Application.WorksheetFunction.Match("Client", Range("A6:A" & _
ActiveSheet.UsedRange.Rows.Count), 0)
Range(CutOff & ":" & ActiveSheet.UsedRange.Rows.Count).Delete

End Sub

Everytime I hit the first "Cut" line I get an error stating "Cut method of
Range class failed". I'm sure I've just got the syntax wrong, but I can't
figure out how to change it. Any help?

 

[Luke]

Just cleaning up original post. Copying from editor to here made the code
look strange.

 

[Don Guillett]

I didn't have data but ran your code. I would have written it differently.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Just cleaning up original post. Copying from editor to here made the code
look strange.

I'm trying to reformat a report I have to run a lot, but I keep getting
an
error whenever I try to cut a range, then insert the cut cells at another
location. Here's my code:

Sub ReformatEEList()

Dim CutOff As Long

Application.ScreenUpdating = False

Range("C5").Insert Shift:=xlToRight
Range("F5:G" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("D5").Insert(xlShiftToRight)
Range("G5:G" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("F5").Insert(xlShiftToRight)
Range("H:I").Insert
Range("H5").Value = "Department"
Range("I5").Value = "Job Title"
Range("M5:M" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("J5").Insert(xlShiftToRight)
Range("R5:R" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("K5").Insert(xlShiftToRight)
Range("M5:N" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("L5").Insert(xlShiftToRight)
Range("R5:R" & ActiveSheet.UsedRange.Rows.Count).Cut
Range("N5").Insert(xlShiftToRight)
Range("P:R").Delete
Range("J:N").NumberFormat = "m/d/yyyy;@"
Range("P:T").NumberFormat = "m/d/yyyy;@"
Range("P6:T" & ActiveSheet.UsedRange.Rows.Count).FormulaR1C1 = _

"=IF(RC[-6]=""00/00/0000"",""00/00/0000"",IF(ISERROR(DATE(YEAR(RC[-6]),MONTH(RC[-6]),DAY(RC[-6]))),RC[-6],DATE(YEAR(RC[-6]),MONTH(RC[-6]),DAY(RC[-6]))))"
Range("P6:T" & ActiveSheet.UsedRange.Rows.Count).Copy
Range("J6").PasteSpecial(Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False)
Range("P:T").Delete
With Range("A5:O" & ActiveSheet.UsedRange.Rows.Count)
.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5"),
Order2:=xlAscending, _
Key3:=Range("B5"), Order3:=xlAscending, Header:=xlYes
End With
CutOff = Application.WorksheetFunction.Match("Client", Range("A6:A" &
_
ActiveSheet.UsedRange.Rows.Count), 0)
Range(CutOff & ":" & ActiveSheet.UsedRange.Rows.Count).Delete

End Sub

Everytime I hit the first "Cut" line I get an error stating "Cut method
of
Range class failed". I'm sure I've just got the syntax wrong, but I
can't
figure out how to change it. Any help?