Shift contents of cells over one column

[chris.ratcliffe]

I am writing a macro that will among other things, move a range of cell data over one column. I am attempting to do something like this:

Please Login or Register  to view this content.

but the .Paste line gives me an error.

Run time error '438':
Object doesn't support this property or method.

Attempting to use PasteSpecial also gives me a (different) error.

I have googled some examples of code snippets that seem to do the same thing, so I believe I must be missing something obvious, but a few hours of poking at this hasn't yielded results. Any help would be appreciated.

Chris

[jeffreybrown]

Hello Chris & Welcome to the Forum,

Why not just shift everything to the right?

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Range("AJ:BE").Rows(ActiveCell.Row).Cells(1, 1).Insert shift:=xlToRight

achieves the same result.

late edit.

Oops! I see Jeff has beaten me to it

[chris.ratcliffe]

Thank you for the welcome, and the quick answers. That almost does what I need, however, the spreadsheet has some conditional formatting and formulas further to the right, and those are being displaced as well. So I guess the difference is that I am trying to shift just the contents (values) over, not the cells themselves. I will keep playing with the Paste and PasteSpecial functions. Thanks.

[jeffreybrown]

Hi Chris,

Let me know how this works out for you...

Please Login or Register  to view this content.

[chris.ratcliffe]

Thanks for the suggestion Jeff. Before I saw your update, I managed to get it working with the copy/paste functions, using the following code:

Please Login or Register  to view this content.

Note the commented out cut and clearcontents lines though. Using either cut, or copy and clearcontents caused the later PasteSpecial to give the same error I received before. I could only do it by just doing a copy, and then later clearing the initial cell that got shifted over. Not sure why, but at this point its academic. Thanks again for your assistance.

Moderator's Note: Please use code tags when posting code.

[jeffreybrown]

Glad to hear you got it working and thanks for the feedback.

==============================================

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save