First Time Using Loops

[Craig13_13]

Hi all,

I have the following code for finding the last column of data, copying it and pasting into the next column (basically to dynamically copy all my formulas over as and when I need more space).

Rather than repeat this same string of code 5 times I believe it is possible to do a loop to repeat this, however I have never managed to use one (sucessfully!) before now, would anyone be able to help please?


Range("A7:A8").Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A7:A8").Select
Selection.End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

 

[Norie]

What is it you need to loop 5 times?

 

[Craig13_13]

Essentially the whole thing, basically I want to find the end column, copy its contents and move them into the following column. My columns all contain formulas and this is the only way I know to ensure that each subsequent column has the correct dynamic formula references in (if that made any sense at all!)

Hopefully some clever brains out there will know an easier way to do this!

 

[Norie]

I understand that, but why do you need to do it 5 times?

This code will copy the last column into the next column.

Dim rngSrc As Range

    Set rngSrc = Cells(7, Columns.Count).End(xlToLeft)

    Set rngSrc = Range(rngSrc, Cells(Rows.Count, rngSrc.Column).End(xlUp))

    rngSrc.Copy rngSrc.Offset(, 1)

To loop it 5 times.

Dim I As Long
Dim rngSrc As Range


   For I = 1 To 5
     Set rngSrc = Cells(7, Columns.Count).End(xlToLeft)

     Set rngSrc = Range(rngSrc, Cells(Rows.Count, rngSrc.Column).End(xlUp))

     rngSrc.Copy rngSrc.Offset(, 1)

   Next I

Or you could do it without a loop.

     Set rngSrc = Cells(7, Columns.Count).End(xlToLeft)

     Set rngSrc = Range(rngSrc, Cells(Rows.Count, rngSrc.Column).End(xlUp))

     rngSrc.Copy rngSrc.Offset(, 1).Resize(, 5)