Previous Topic Next Topic

Splite the multiple column value copy & paste in other sheet

Solved
Report
Posts
1
Registration date
Thursday December 31, 2020
Status
Member
Last seen
December 31, 2020
 -
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
 -
Hello,
I want to copy & paste the value of my data sheet, value are save in column & row, i want to splite the spacific rage of column value copy & paste in other sheet in with same value as give example in image

thanks in advance for help i try to make macro but it not work for entire sheet


System Configuration: Windows / Chrome 87.0.4280.88
Related:

1 reply

Answer 1 / 1
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
 490
Hi Nitesh,

Create your destination sheet, call it "Sheet2" and add the header in range B1:W1.

Then run the following code: 
Sub RunMe()
Dim x, sR, dR, sC, lRow As Long

Sheets("Sheet1").Select
lRow = Range("B" & Rows.Count).End(xlUp).Row
dR = 2

For sR = 2 To lRow
    x = 1
    sC = 24
    Range(Cells(sR, "B"), Cells(sR, "W")).Copy
    Sheets("Sheet2").Select
    Range(Cells(dR, "B"), Cells(dR + 6, "B")).Select
    Selection.PasteSpecial
    Sheets("Sheet1").Select
    Do
        Range(Cells(sR, sC), Cells(sR, sC + 10)).Copy Sheets("Sheet2").Range("K" & dR + x)
        x = x + 1
        sC = sC + 13
    Loop Until x = 7
    dR = dR + 7
Next sR
End Sub


Best regards,
Trowa

Reply Ask a question