Copying values from one column and inserting below the values of another

[Dougie4540]

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

 

[HalfAce]

Hi, and welcome to the board.
If I understand what you're doing then perhaps this will help.
It doesn't care how many columns there are, or how many rows there are, it'll copy columns C through (whatever) to column B in the next available row.
It does not take any header row(s) into account, which you may want to amend but it wasn't mentioned so I didn't do it.
Anyway, give this a try and see if it gets you close to where you want to be.

Sub CopyColumnsToB()
Dim LstCol As Long, ColToCopy As Long
LstCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
ColToCopy = 3
While ColToCopy < LstCol + 1
  Range(Cells(1, ColToCopy), Cells(Rows.Count, ColToCopy).End(xlUp)).Copy _
    Cells(Rows.Count, "B").End(xlUp)(2)
  ColToCopy = ColToCopy + 1
Wend
End Sub

Hope it helps.

 

[Dougie4540]

Hi,

Thanks for the quick reply. The coding you suggest works perfectly. You have saved me hours of data processing time!!!

Thanks
Dave

 

[HalfAce]

You're most welcome. Glad it helped.