First Time Using Loops

Craig13_13

New Member
Joined
Apr 9, 2009
Messages
21
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
What is it you need to loop 5 times?
 

Craig13_13

New Member
Joined
Apr 9, 2009
Messages
21
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
I understand that, but why do you need to do it 5 times?

This code will copy the last column into the next column.
Code:
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.
Code:
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.
Code:
     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)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,803
Messages
5,655,391
Members
418,195
Latest member
LabraLime

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top