Copy column loop

CZ191279

New Member
Joined
Mar 20, 2019
Messages
3
Dear all,
May I ask your help for a macro which allowed me to copy a range (column) 16350 times :)
On D5 to D369 I have a formula which do a random calculation.
On F4 to XEA4 I have a number from 1 to 16,350
I need a macro which copy the range D5 to D369 on range F5 to F369 (F4=1);G5 to G369 (F5=2) and so on till XEA5 which is the 16,350 columns...
I need the macro ended when it reach the number 16,350 or XEA4
thank you very much for your help
I hope I was clear as a beginner in VBA
thank you
CZ
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,472
Office Version
  1. 2010
Platform
  1. Windows
try this:
Code:
For i = 1 To 16350
inarr = Range("D5:D369")
Range(Cells(5, 5 + i), Cells(369, 5 + i)) = inarr
Next i
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
How about:

Code:
Sub copycolumn()
    Range("D5:D369").Copy Range("F5:XEA5")
End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Code:
[D5:D369].Copy [F5:XEA369]
 
Last edited:

CZ191279

New Member
Joined
Mar 20, 2019
Messages
3

ADVERTISEMENT

Thank you very much for the answer but it is not so easy as there is a randow formula on column D i need to copy this column 16350 as i need to have 16350 different result (everytime I copy paste one column the random variable is updated) So I need everytime to copy column D and paste it on F recopy D copy on G .... thank you
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try:

Code:
Sub copycolumn2()
    For i = Columns("F").Column To Columns("XEA").Column
        Range("D5:D369").Copy Cells(5, i)
    Next
End Sub
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,472
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Have you tried my solution post #2 , I anticipated that you wanted a recalculation in between each copy and my code will do that. I tested it with RAND() functions in column D and each column copied was different
You can force a recalculation by adding this code if you find it necessary:
Code:
Sub test()

For i = 1 To 16350
inarr = Range("D5:D369")
Range(Cells(5, 5 + i), Cells(369, 5 + i)) = inarr
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
Next i
End Sub
 

CZ191279

New Member
Joined
Mar 20, 2019
Messages
3
Thank you all for your time after trying the prog of offthelip my file works perfectly thank you again
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,466
Members
409,883
Latest member
asharris90
Top