PDA

View Full Version : Copying values from one column and inserting below the values of another



Dougie4540
01-18-2013, 05:35 PM
Dear forum users,

I have a large data set in excel; 184 rows per column and roughly 569 columns. I want to copy the cells from column C and paste these below the values in column B with no gap. I then wish to copy cells from column D and place them below the column C values in Column B. I am trying to create a macro that will allow me to do this for all the columns; however as I have no VBA or macro experience I have been using the record fuction to look at the code and write it that way. However this leads to lengthy coding as follows:

Range("C1:C184").Select
Selection.Cut
ActiveWindow.SmallScroll Down:=174
Range("B185").Select
ActiveSheet.Paste

I then repeat this coding for each column adding 184 to the cell to paste into. Is there a quicker way of doing this without having to write the same code for each column e.g. some way to loop the code? Any advice would be greatly appreciated.

Thanks in advance,
Dave :dunno

Simon Lloyd
01-19-2013, 01:06 AM
This should do what you need for all 569 columns :)
Sub copy_it()
Dim i As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
For i = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 1
Range(Cells(1, i).Address & ":" & Columns(i).End(xlUp).Address).Copy Destination:=Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Next i
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub