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
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 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

1 reply

Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!