Hi Anup,
So you want you end result to look like this:
It not clear to me though, what you starting situation looks like.
If it looks like this:
Then use this code:
Sub RunMe()
Dim x, lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To lRow Step 5
Range("A" & x, "B" & x).Copy Range("A" & x + 1, "B" & x + 4)
Range("C" & x, "F" & x).Copy
Range("C" & x + 1).PasteSpecial Transpose:=True
Next x
Application.CutCopyMode = False
End Sub
When your data looks like this:
Then use this code:
Sub RunMe()
Dim x, lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To lRow * 5 Step 5
Rows(x + 1 & ":" & x + 4).Insert
Range("A" & x, "B" & x).Copy Range("A" & x + 1, "B" & x + 4)
Range("C" & x, "F" & x).Copy
Range("C" & x + 1).PasteSpecial Transpose:=True
Next x
Application.CutCopyMode = False
End Sub
If you are unfamiliar with codes then read the following:
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Best regards,
Trowa
Yes, it was a mistake and I wanted to divide the volume un A to B and C column. I was able to come up with the same formula. Thank you very much for your quick reply.
But I have now a new challenge. Can you please help me.
Suppose I have one row with data in column A,B,C,D,Eand F.
Now suppose the name in A and B has to be repeated 4 times in 4 rows but C D and E will come in different rows.
Eg I want my one row to be divided in 5 rows with the following combination.
1st row A B C
2nd row A B D
3rd row A B E
4th row A B F
I have like this thousands of rows for which I would like to then repeat this formula so that same thing happens to every different row
Could you please help.
Best Regards,
Anup