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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
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,300
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)
 

Forum statistics

Threads
1,136,787
Messages
5,677,731
Members
419,716
Latest member
MPunt

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