Cut/paste range within sheet & Runtime error 1004

[flip]

Hi all,

I have a sheet that produces a range of results (ID numbers) starting at column P and extending for anything up to 11 columns to the right, i.e. could end at column Z but could also be just 2 columns of data, ending at column Q. I want to "align" all this data so that the LAST column of data is always AA, i.e. have the blanks on the left not the right.

so conceptually from this:
(1) (4) (19) (6) (empty) (empty) (empty)

I want to rearrange like:
(empty) (empty) (empty) (1) (4) (19) (6)

(all starting from col.P as the leftmost column with data and ending up with everything shifted across to end at col.AA)

I've already put a formula into col.AB that returns the variable "SIDOffset" as the number of blanks in range P:AA, i.e. if there are 8 ID numbers (P, Q, R, S populated, all others blank) then this will show 4.

I have tried the following code but it doesn't work: Run-time error '1004', Application-defined or object-defined error. The problematic line is highlighted below

With Sheets(TargetSheet)
* other code*
For Each cell3 in .Range("P2:P6")
SIDOffset=.Range("AB" & cell3.Row).Value
.Range(Cells(cell3.Row, 16), Cells(cell3.Row, 16 + (11 - SIDOffset))).Cut
.Range(Cells(cell3.Row, 16 + SIDOffset), Cells(cell3.Row, 27)).Paste

Tried the same thing referencing Sheets(TargetSheet).Range(cells(cell3.Row etc. but it still didn't work.

So I even tried this, just copying data across before emptying the cells to the left, but it also doesn't work:

With Sheets(TargetSheet)
* other code*
For Each cell3 in .Range("P2:P6")
SIDOffset=.Range("AB" & cell3.Row).Value
.Range(Cells(cell3.Row, 16 + SIDOffset), Cells(cell3.row, 27)).Value = .Range(Cells(cell3.Row, 16), Cells(cell3.Row, 16 + (11 - SIDOffset))).Value

Thanks very much to anyone who can help, appreciate your time!

 

[Z51]

Not tested, but try putting a period before the "Cells(..." references, too, since you're referencing it inside the With Sheets(... / End With.

 

[rollis13]

Hi to all.
This is what I came up with, hope it helps:

    Application.ScreenUpdating = False            '<- added
    With Sheets(TargetSheet)
        For Each cell3 In .Range("P2:P6")
            SIDOffset = .Range("AB" & cell3.Row).End(xlToLeft).Column '<- changed
            If SIDOffset < 16 Then GoTo skipempty '<- skip if row is empty (maybe not necessary, added just to be sure)
            .Range(Cells(cell3.Row, 16), Cells(cell3.Row, SIDOffset)).Cut
            .Cells(cell3.Row, 27 - (SIDOffset - 16)).Select '<- changed
            Sheets(TargetSheet).Paste             '<- added
skipempty:                                        '<- added
        Next cell3
    End With
    Application.ScreenUpdating = True             '<- added