Error in Macro: "Method 'Paste' of object '_Worksheet' failed"

[blork]

Hi,
I'm pretty new to working with VB, so excuse me if this is just
something simple I have overlooked. I have a fairly simply macro that
moves all the Cells in a range down the sheet by 1 (by cutting and
pasting). This initially seemed to work, but now i get a "Method
'Paste' of object '_Worksheet' failed" error when I run it. This is the
full macro:

Range("L1:AH500").Select
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollRow = 1
Application.CutCopyMode = False
Selection.Cut
Range("L2").Select
ActiveSheet.Paste
Range("I2:I24").Select
Selection.Copy
Range("L1:AH1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

If anyone can help me, please do!

 

[Carim]

Hi Blork,

Following should do the job :
Range("L1:AH500").Select
Selection.Cut
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select

Beware of your range selection, if you want to repeat the process ...
HTH
Cheers
Carim

 

[blork]

Thanks for your help,
Unfortunately it's still returning with the exact same error, and when
I go to debug the code, "ActiveSheet.Paste" is still highlighted in
yellow.

 

[Carim]

Blork,

Is your copy paste done within the same worksheet or not ?

 

[blork]

Originally I was attempting to copy/paste from a seperate Workbook, but
when that failed (with that error), I tried it from within the same
sheet, with a modified macro. So, the code that I pasted up there ^^,
that errors out, is working within the same sheet.

 

[Carim]

Blork,

I guess you mean to copy-paste between different worksheets within the
same workbook ...
The workbook is equivalent to an excel File, and the worksheets are
represented by the different tabs sheet1, sheet2, sheet3 ...
If you want to operate between sheets, you only have to be specific in
your range definition, and specify which destination sheet you want ...
e.g.

Worksheets("Sheet1").Range("L1:AH500").Select ' for your input
Worksheets("Sheet2").Range("L2").Paste ' for your destination

HTH
Cheers
Carim

 

[blork]

No, I know this....let me explain.
I was initially attempting to expand a macro I had earlier created to
carry out another function. This macro is located in one workbook (say,
"one.xls"). I wanted to be able to copy/paste the values in my 2nd
workbook ("two.xls") from a button located in "one.xls". When this
failed with *the* error, I decided to simplify matters, and have the
function take place in one workbook, "two.xls", copying and pasting
values in one sheet. This failed too. So now I'm here. Bit complicated,
I know.

All I'm asking for help with is the code I posted, which copies in the
same sheet.

Thanks.

 

[Tom Ogilvy]

With Workbooks("Two.xls")
.Activate
With .Worksheets("Sheet1")
.Activate
.Range("L1:AH500").Select
Selection.Cut
.Range("L2").Select
ActiveSheet.Paste
.Range("I2:I24").Select
Selection.Copy
.Range("L1:AH1").Select
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:= _
False, Transpose:=True
End with
End With

Change Sheet1 to reflect the name of the Sheet in worbook Two.xls