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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
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
75,362
Office Version
365
Platform
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,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top