• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and paste range from column to row and repeat in every nth row

Dear All,

I have two sheets. Sheet1 having three columns (A to C) and i need to select repeatedly every 1 to 10 till blank and paste it transpose to sheet2 in rows 8 to 10, 13 to 15, 18 to 20 till blank in sheet1 column a to c.

second issue in sheet2 from column A to J row 6 to 7 copy and paste in 11 to 12, 16 to 17, 21 to 22 and so on (i.e. total count of column A sheet1 times).

I have attached file: Sheet1 data and sheet2 required output.

Kindly assist me.
 

Attachments

  • Copy paste.xlsx
    16.3 KB · Views: 8
Hi !

A demonstration as a beginner starter :​
Code:
Sub Demo1()
    Dim L&, R&
        Application.ScreenUpdating = False
        L = 3
    For R = 1 To Sheet1.UsedRange.Rows.Count Step 10
        L = L + 5
        Sheet2.Cells(L, 1).Resize(3, 10).Value = Application.Transpose(Sheet1.Cells(R, 1).Resize(10, 3))
    Next
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Dear Sir,
Macro working perfectly. Thank you sir.

second issue in sheet2 from column A to J row 6 to 7 copy and paste in 11 to 12, 16 to 17, 21 to 22 and so on (i.e. total count of column A sheet1 times).
 
second issue in sheet2 from column A to J row 6 to 7 …
Code:
Sub Demo2()
  Const N = 10
    Dim L&, R&
        Application.ScreenUpdating = False
        L = 1
    For R = 1 To Sheet1.UsedRange.Rows.Count Step N
        L = L + 5
        Sheet2.Cells(L, 1).Resize(2, N).Value = [{"AAA";"BBB"}]
        Sheet2.Cells(L + 2, 1).Resize(3, N).Value = Application.Transpose(Sheet1.Cells(R, 1).Resize(N, 3))
    Next
        Application.ScreenUpdating = True
End Sub
You may Like it !
 
Back
Top