Duplicating Macro
Closed
Ceri88
Posts
2
Registration date
Thursday August 7, 2014
Status
Member
Last seen
August 20, 2014
-
Aug 7, 2014 at 06:08 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 1, 2014 at 11:47 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 1, 2014 at 11:47 AM
Related:
- Duplicating Macro
- Spell number in excel without macro - Guide
- Macro excel download - Download - Spreadsheets
- How to stop itunes from duplicating songs - Guide
- Automatically run a macro when opening a worksheet - Guide
- Excel macro to create new sheet based on value in cells - Guide
4 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 7, 2014 at 11:16 AM
Aug 7, 2014 at 11:16 AM
Hi Ceri88,
How do we know which data needs to go in which column?
I mean "OVAL" needs to be repeated 3 times and placed in column A.
Then "1.1" needs to be repeated 1 time and placed in column B.
How do we know that "1.1" needs to go in column B and not A. Is it because it is a numeric value?
Best regards,
Trowa
How do we know which data needs to go in which column?
I mean "OVAL" needs to be repeated 3 times and placed in column A.
Then "1.1" needs to be repeated 1 time and placed in column B.
How do we know that "1.1" needs to go in column B and not A. Is it because it is a numeric value?
Best regards,
Trowa
Hi
It doesn't matter which way round they appear as long as one description is in one column and the other in another.
The data starts off like this
3 Oval
1 1.1
1 1.2
1 1.3
The macro in the first comment converted it to show
oval
Oval
Oval
1.1
1.2
1.3
And I wanted them next to each other, if this makes anymore sense lol
Thanks
It doesn't matter which way round they appear as long as one description is in one column and the other in another.
The data starts off like this
3 Oval
1 1.1
1 1.2
1 1.3
The macro in the first comment converted it to show
oval
Oval
Oval
1.1
1.2
1.3
And I wanted them next to each other, if this makes anymore sense lol
Thanks
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 11, 2014 at 10:50 AM
Aug 11, 2014 at 10:50 AM
Hi Cezza,
Not sure how the rest of your data looks like, but looking at your example the following code will do the trick:
NOTE: Source data = Column A:B | Destination data = Column C:D
I'm pretty sure this will not work for the rest of your data, but maybe my question will make more sense now ;).
Best regards,
Trowa
Not sure how the rest of your data looks like, but looking at your example the following code will do the trick:
NOTE: Source data = Column A:B | Destination data = Column C:D
Sub RunMe() Dim x, y, z As Integer Dim lRow As Long Dim SecondCol As Boolean lRow = Range("B1").End(xlDown).Row For Each cell In Range("B1:B" & lRow) x = cell.Offset(0, -1) If SecondCol = False Then Do y = y + 1 Cells(y, "C").Value = cell.Value x = x - 1 Loop Until x = 0 SecondCol = True Else Do z = z + 1 Cells(z, "D").Value = cell.Value x = x - 1 Loop Until x = 0 End If Next cell End Sub
I'm pretty sure this will not work for the rest of your data, but maybe my question will make more sense now ;).
Best regards,
Trowa
Ceri88
Posts
2
Registration date
Thursday August 7, 2014
Status
Member
Last seen
August 20, 2014
Aug 20, 2014 at 07:18 AM
Aug 20, 2014 at 07:18 AM
I have added a picture of the excel report, ideally what I was looking for was for the name of the item to repeat itself based on the figure to the left of it, then the size of the item to repeat itself based of the figure to the left of it. The macro I first copied in does this for me which is great. I just want it to copy them and place them next to each other instead of underneath each other. The finished item would have column A with the repeated name of the item then column B with the repeated sizes of the item.
:)
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Sep 1, 2014 at 11:47 AM
Sep 1, 2014 at 11:47 AM
Hi Ceri,
Your picture definitely helps.
So your picture sheet is called "DataToCopy" and your destination sheet is called "Result".
Let's see if the following code brings us closer to the solution:
Best regards,
Trowa
Your picture definitely helps.
So your picture sheet is called "DataToCopy" and your destination sheet is called "Result".
Let's see if the following code brings us closer to the solution:
Sub RunMe() Dim x, y, z As Integer Dim lRow As Long Dim SecondCol As Boolean Sheets("Result").Columns("A:B").ClearContents Sheets("DataToCopy").Select lRow = Range("E10").End(xlDown).Row For Each cell In Sheets("DataToCopy").Range("E10:E" & lRow) x = cell.Offset(0, -2) If SecondCol = False Then Do y = y + 1 Sheets("Result").Cells(y, "A").Value = cell.Value x = x - 1 Loop Until x = 0 SecondCol = True Else Do z = z + 1 Sheets("Result").Cells(z, "B").Value = cell.Value x = x - 1 Loop Until x = 0 End If Next cell End Sub
Best regards,
Trowa