Looping Range problems

[Schnappa]

Hoping you may be able to help. Being a newbie, this one has me stumped...

Trying to start from cell a1, and copy the range of data from b1 to i1 to position k1. I need this to be loopped so the same is done for all lines of data from a1, a3, a5, a6 etc until cell ax is blank. Having red quote a few articles and playing around with their suggestion, the code below represents my work thus far, and is also the source of my frustration.

You will note that I am trying to use formulas to perform the copy / paste within the range codes, and I assume this is where the problem lay.

Excel is saying that line 9 is causing the problem. Is anyone able to explain the correct syntax?

Thanks

GV

Sub CutPaste()
Dim TopLeft As Range ' Not sure if this is valid as I need the cell reference.
Dim TopRight As Range
Dim DestLeft As Range
Do While ActiveCell.Select <> Empty
Set TopLeft = ActiveCell.Offset(0, 1) ' Brings back the contents of the cell , not the cell reference (need the cell reference)
Set TopRight = ActiveCell.Offset(0, 8) ' As above
Set DestLeft = ActiveCell.Offset(0, 10) ' As above
Range("TopLeft : TopRight").Select ' This is where excel has a problem
Selection.Copy
Range("DestLeft").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 0).Select
Loop
End Sub

 

[combo]

Change to:
Range(TopLeft, TopRight).Select

Your code would be shorter, faster and easier to understand if you assign ActiveCell to variable (Range) in the beginning and refer to it for copy/paste action without selecting.

combo

 

[macropod]

Hi Schnappa,

Is there any reason you skipped rows 2 & 4? And is there a column that you can test that always has data in all the rows you want until the first empty row is encountered? If there's no need to skip rows 2 & 4 and there is a suitable test column, you could use something like:

Sub CutPaste()
Dim i As Long
With ActiveSheet
    For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
        If .Cells(i, 1) = "" Then Exit For
    Next
    .Range("B1", .Cells(i, 9)).Cut
    .Paste Destination:=.Range("K1")
End With
End Sub

where you change the '1' in '.Cells(i, 1)' to that column's index number.

if you need to skip rows 2 & 4 but there is a suitable test column, then you could use something like:

Sub CutPaste()
Dim i As Long
With ActiveSheet
    .Range("B1:I1)).Cut
    .Paste Destination:=.Range("K1")
    .Range("B3:I3)).Cut
    .Paste Destination:=.Range("K3")
    For i = 5 To .Cells.SpecialCells(xlCellTypeLastCell).Row
        If .Cells(i, 1) = "" Then Exit For
    Next
    .Range("B5", .Cells(i, 9)).Cut
    .Paste Destination:=.Range("K5")
End With
End Sub

Cheers

[MS MVP - Word]

 

[SkipVought]

Hi,

I recommend avoiding the use of the Select and Activate methods for navigating sheets and ranges.

Your code ASSUMES a certain sheet and cell is active. Take that ambiguity away and explicitly specify or calculate the starting reference for this procedure.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}