Trouble with Cutting and Inserting a Range
in [Programming]
|
From: Luke on 8 Feb 2010 10:32 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?
From: Luke on 8 Feb 2010 11:14 Just cleaning up original post. Copying from editor to here made the code look strange. "Luke" wrote: > 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?
From: Don Guillett on 8 Feb 2010 11:49 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 dguillett(a)gmail.com "Luke" <Luke(a)discussions.microsoft.com> wrote in message news:D7F2D4B9-FF1A-492D-A9E5-FA741E5D77F4(a)microsoft.com... > Just cleaning up original post. Copying from editor to here made the code > look strange. > > "Luke" wrote: > >> 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?
|
Pages: 1 Prev: Ribbon callback from built-in buttons to trigger additional code Next: Last row + rank help |