VBA Copy and Paste Macro Excel for range of cells to populate in column and then pick next range

[besucool]

Hi folks, been wrecking my brain on this.

Basically I want to copy and paste H1:H10 into C1:C10 and then pick next column of I1:I10 copy into next blank area of column C which is C11:C20; then copy next column J1:J10 into C21:30; then K1:K10 into C31:40 etc etc etc. This repeats itself for about 60 times from columns from H to BO.

So I am struggling picking a range of cells from column H after copying to move to next range on the right and copying into the next blank section in column C the target column.

Any help would be great.

Thanks,

 

[Tim_Excel_]

With Workbooks("[B]NAME[/B]").Worksheets([B]NUMBER[/B])
For lCol = 8 To.UsedRange.Columns.Count

Set rng = .Range(Cells(1, lCol)[COLOR=#000000][FONT=Consolas], Cells(10, lCol)[/FONT][/COLOR])
For each Column in Workbooks("[B]NAME[/B]").Worksheets([B]NUMBER[/B])
Range(rng).Copy
'paste code
Next

Try this out

 

[MARK858]

Another possible option (please note for the active sheet only as you haven't stated the sheet)....

Sub xxx()
    Dim i As Long, x As Range
    Application.ScreenUpdating = False
    For i = 8 To 67
        Set x = Range("C" & Rows.Count).End(xlUp)
        If x.Row = 1 Then
            Range(Cells(1, i), Cells(10, i)).Copy Cells(1, "C")
        Else
            Range(Cells(1, i), Cells(10, i)).Copy x.Offset(1)
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Using an array would be faster but I'm just going out so no time to write...

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    'If you want to do this till last column with data in row(1)
    'Change 67 to "LastColumn"
    For i = 8 To 67
        Lastrow = Cells(Rows.Count, "C").End(xlUp).Row + 1
        Range(Cells(1, i), Cells(10, i)).Copy Destination:=Cells(Lastrow, 3)
    Next
Application.ScreenUpdating = True
End Sub

OK

 

[MARK858]

Hi M.A.I.T, just to say that the code you posted won't start in C1 per the OP's request (as far as I can see as I am not by a computer now) the +1 should make it start in C2.

paste H1:H10 into C1:C10

 

[My Aswer Is This]

Try this:
I had to make a correction.

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = 1
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    'If you want to do this till last column with data in row(1)
    'Change 67 to "LastColumn"
    For i = 8 To 67
        Range(Cells(1, i), Cells(10, i)).Copy Destination:=Cells(Lastrow, 3)
        Lastrow = Cells(Rows.Count, "C").End(xlUp).Row + 1
  
    Next
Application.ScreenUpdating = True
End Sub

OK

 

[My Aswer Is This]

Your correct. I missed that thanks. I have made a correction.

Hi M.A.I.T, just to say that the code you posted won't start in C1 per the OP's request (as far as I can see as I am not by a computer now) the +1 should make it start in C2.

 

[besucool]

Hi M.A.I.T and MARK858 both seem to sort of work and certainly points me in the right direction. Totals match up from H to BO on column C, but I would expect it to fill from C1:C600 however it only goes up to C594. There are blanks in some cells in H to BO of my data so I'm wondering if that has anything to do with it?

Thank so much

 

[My Aswer Is This]

Yes if some rows in the columns are empty it column throw off things.

I will see if I can fix that .

 

[My Aswer Is This]

Try this:

This will copy over all rows even if they are empty.

Sub Test()
'Modified
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = 1
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    'If you want to do this till last column with data in row(1)
    'Change 67 to "LastColumn"
    For i = 8 To 67
        Range(Cells(1, i), Cells(10, i)).Copy Destination:=Cells(Lastrow, 3)
        Lastrow = Lastrow + 10
  
    Next
Application.ScreenUpdating = True
End Sub

OK