Macro question in Excel

[absthechatter]

I have a Macro, to copy two columns and paste in, further to the left;

Next lngLoop
Sheets("CCReport").Activate
Columns("E:F").Select
Application.CutCopyMode = False
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft

Range("B1").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft

This works absoloutely fine on my home computer (Office 2003), but whenever I try it at work (Office 2000), I get the information cannot be pasted because the cut area and paste area are not the same size (Run-time error '1004'.

Is this just a problem with earlier versions of Excel, or is there a way i can fix it so it runs on my work computer.

Many thanks

Abs

[gazzae]

Why have you a NEXT at the top? That probably causing the error as there is no FOR. Assuming that is your whole code of course.

[PC_Arcade]

Originally Posted by absthechatter

Next lngLoop
Sheets("CCReport").Activate
Columns("E:F").Select
Application.CutCopyMode = False
Selection.Cut
Range("B1").Select
Selection.Insert Shift:=xlToRight
Range("b2").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft

Range("B1").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft

This works absoloutely fine on my home computer (Office 2003), but whenever I try it at work (Office 2000), I get the information cannot be pasted because the cut area and paste area are not the same size (Run-time error '1004'.

Is this just a problem with earlier versions of Excel, or is there a way i can fix it so it runs on my work computer.

Many thanks

Abs

I've changed the bold lines above, does that work?
It's to do with the fact that according to excel 2000 you're trying to past two columns (E&F) into one (B) if you select the cell rather than the column before you paste it should insert correctly

I use macros all the time, but it's been a while since I've used excel2000

[gazzae]

The code works fine in Excel 2000 once the next line is removed.

---------- Post added at 10:32 ---------- Previous post was at 10:20 ----------

Macros aren't very efficent.

This performs the same function in half the lines

With Worksheets("ccreport")
.Range("E:F").Cut
.Range("B:C").Insert
.Range("b1:c2").ClearContents
End With
Application.CutCopyMode = False

[absthechatter]

Originally Posted by gazzae

Why have you a NEXT at the top? That probably causing the error as there is no FOR. Assuming that is your whole code of course.

Thanks Gazzae, it is not the whole code, only a small part of it. Everything works fine up to this point, and then it has problems with the pasting. Have tried the two methods and still not working on Excel 2000, although fine on 2003.

Abs

---------- Post added at 11:15 ---------- Previous post was at 11:09 ----------

Actually starting to get somewhere now, with your shortened code gazzae!! It is now cutting columns E & F and attempting to paste, but I get cannot change part of a merged cell. Any ideas?

Abs

[gazzae]

In my code if I make a merged cell from D1 to F1 then it causes the same error as you.

[absthechatter]

Got away with....

Next lngLoop
Sheets("CCReport").Activate
Columns("E:F").Select
Application.CutCopyMode = False
Selection.Cut
Range("B1").Insert
Range("B2").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft

Range("B1").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft



It appears to work. Probably more than I need to write... but!!

Many thanks for all your help.

Abs

[gazzae]

Most have been your merged cells that were causing the error.

As for the last 6 lines of your code you can simply use..

Range("b1:c2").Delete