Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Trouble with Cutting and Inserting a Range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 142
Default Trouble with Cutting and Inserting a Range
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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 142
Default Trouble with Cutting and Inserting a Range
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?
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,522
Default Trouble with Cutting and Inserting a Range
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

"Luke" wrote in message
...
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?

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem inserting rows (and cutting) jodleren Excel Discussion (Misc queries) 2 January 8th 10 06:16 PM
Trouble cutting a range of cells Tim Excel Programming 5 June 8th 08 08:14 PM
Having Trouble Inserting a Column Elisa Excel Worksheet Functions 1 January 23rd 08 04:37 PM
Cutting rows and inserting in another sheet Karen Bates Excel Discussion (Misc queries) 5 May 23rd 07 08:23 PM
Macro trouble: inserting 'watermark' ER Excel Programming 4 February 27th 04 08:01 PM


All times are GMT +1. The time now is 03:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement