How to copy and paste in a loop in a set of 3 cells

C

cubs786

New Member
Joined
May 28, 2019
Messages
4
Hi,

I have an excel with lots of data but the data is in columns (each column has 10,000 or more data points), such as:

7.52
7.56
7.56
7.55
7.35

and I want copy the first three cells and transpose them on a different excel file, such as

7.52, 7.56, 7.56
7.56, 7.55, 7.35

But I want to do the whole column instead of manually copying and pasting three cells at a time.

I will be using a macro to do this job.

I know how to copy and paste the first three cells using macro but I want to continue all of the data to run in a loop to finish the job automatically or there is any other to do this?

thanks.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
The source column 1, do you want it in the destination columns 1,2 and 3, where do you want the origin column 2, in destination columns 4,5 and 6?How many origin columns do you have?
 
Upvote 0
Via Non-Macro:

If you put this formula in the first cell where you want the result(s) to be, fill across 2 more cells so you have the formula in 3 cells, then fill down as many as the (#rows)/3 or until you get all 0s in the rows.

Code: 
=INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))

You could incorporate a test for all 0s and blank them or do whatever.
 
Last edited:
Upvote 0
If so, try this
change sheet1 by the name of your source sheet

(2 columns with 10,000 records each, processed in 5 seconds)

Code: 
Sub set3column()
    Dim l1 As Workbook, l2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
    Dim i As Long, j As Long, k As Long, c As Long
    
    Application.ScreenUpdating = False
    Set l1 = ThisWorkbook
    Set sh1 = l1.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    Set l2 = Workbooks.Add
    Set sh2 = l2.Sheets(1)
    
    c = 1
    For j = 1 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column
        k = 1
        For i = 1 To sh1.Cells(Rows.Count, j).End(xlUp).Row Step 3
            sh2.Cells(k, c).Resize(1, 3).Value = WorksheetFunction.Transpose(sh1.Cells(i, j).Resize(3, 1).Value)
            k = k + 1
        Next
        c = c + 3
    Next
    MsgBox "End"
End Sub
 
Upvote 0
I had a different macro approach which seemed to work if I understand the OP correctly.

Code: 
Sub TransposeBy3()
Dim LR As Long
LR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LR = LR / 3
Sheets("Sheet1").Range("C1:E1").Formula = "=INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))"
Range("C1:E1").Select
Selection.AutoFill Destination:=Range("C1:E" & LR)
End Sub
 
Upvote 0
DanteAmor said:
The source column 1, do you want it in the destination columns 1,2 and 3, where do you want the origin column 2, in destination columns 4,5 and 6?How many origin columns do you have?
Click to expand...

column A

7.52
7.56
7.56
7.55
7.35

I want to have it in the same excel file


B C D
7.52, 7.56, 7.56
7.56, 7.55, 7.35

Hopefully it clears it up



 
Upvote 0
AH, the 3rd value becomes the first in the next set of 3 transpositions?!?!?
 
Upvote 0
I apologize for the typo but it should be the fourth one.

7.52
7.56
7.56
7.55
7.35
7.25


B C D
7.52, 7.56, 7.56
7.55, 7.35, 7.25
 
Upvote 0
Then I think my formulas or my macro will do it for you, right? I just tried 10005 cells and the macro did it instantaneously.
 
Upvote 0
Thanks, it's working perfectly fine. but is there a way to add if and else statement to take out the zeros at the end.

[TABLE="width: 576"]
<colgroup><col width="72" span="8" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72, align: right"]73.53906[/TD]
[TD="width: 72, align: right"]73.53636[/TD]
[TD="width: 72, align: right"]73.55796[/TD]
[TD="width: 72, align: right"]73.44028[/TD]
[TD="width: 72, align: right"]73.48685[/TD]
[TD="width: 72, align: right"]73.53033[/TD]
[TD="width: 72, align: right"]73.58295[/TD]
[TD="width: 72, align: right"]73.49767[/TD]
[/TR]
[TR]
[TD="align: right"]73.5328[/TD]
[TD="align: right"]73.48087[/TD]
[TD="align: right"]73.53788[/TD]
[TD="align: right"]73.50001[/TD]
[TD="align: right"]73.51572[/TD]
[TD="align: right"]73.56081[/TD]
[TD="align: right"]73.53013[/TD]
[TD="align: right"]73.52189[/TD]
[/TR]
[TR]
[TD="align: right"]73.47843[/TD]
[TD="align: right"]73.56998[/TD]
[TD="align: right"]73.55529[/TD]
[TD="align: right"]73.49907[/TD]
[TD="align: right"]73.50033[/TD]
[TD="align: right"]73.55747[/TD]
[TD="align: right"]73.51356[/TD]
[TD="align: right"]73.55635[/TD]
[/TR]
[TR]
[TD="align: right"]73.55299[/TD]
[TD="align: right"]73.53053[/TD]
[TD="align: right"]73.56288[/TD]
[TD="align: right"]73.52654[/TD]
[TD="align: right"]73.49673[/TD]
[TD="align: right"]73.53942[/TD]
[TD="align: right"]73.5472[/TD]
[TD="align: right"]73.54499[/TD]
[/TR]
[TR]
[TD="align: right"]73.54589[/TD]
[TD="align: right"]73.52994[/TD]
[TD="align: right"]73.53255[/TD]
[TD="align: right"]73.47897[/TD]
[TD="align: right"]73.51845[/TD]
[TD="align: right"]73.48042[/TD]
[TD="align: right"]73.52837[/TD]
[TD="align: right"]73.49318[/TD]
[/TR]
[TR]
[TD="align: right"]73.54636[/TD]
[TD="align: right"]73.50286[/TD]
[TD="align: right"]73.49793[/TD]
[TD="align: right"]73.5041[/TD]
[TD="align: right"]73.54249[/TD]
[TD="align: right"]73.48727[/TD]
[TD="align: right"]73.46145[/TD]
[TD="align: right"]73.48816[/TD]
[/TR]
[TR]
[TD="align: right"]73.53129[/TD]
[TD="align: right"]73.56045[/TD]
[TD="align: right"]73.54726[/TD]
[TD="align: right"]73.45769[/TD]
[TD="align: right"]73.51023[/TD]
[TD="align: right"]73.50892[/TD]
[TD="align: right"]73.58716[/TD]
[TD="align: right"]73.49274[/TD]
[/TR]
[TR]
[TD="align: right"]73.50082[/TD]
[TD="align: right"]73.52829[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
How can I sort rows into different worksheets?
Replies
2
Views
143
nithchun
N
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
438
ChrisMac1
ChrisMac1
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
1K
Micron
M
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
189
sijpie
sijpie
S
  • Question Question
Look up todays date and special copy paste value for multiple sheets
Replies
4
Views
228
OaklandJim
O

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top