Previous Topic Next Topic

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 Contributor Last seen December 27, 2022 - Sep 1, 2014 at 11:47 AM
Hi

I currently have the below macro to duplicate rows for me but I want it to show the data in 2 columns instead of nderneath each other. EG

OVAL
OVAL
OVAL
1.1
1.2
1.3 currently how the results are shown

OVAL 1.1
OVAL 1.2
OVAL 1.3 how I want the results shown

the macro I'm using is this one, do I need to write a completely new one or can I amend this one?

Sub CopyCells()


Dim myText As String
Dim intRow, c_Counter, timesToDuplicate, counterIndex As Integer

intRow = 2
counterIndex = 0

Worksheets("Result").Range("A:A").Value = ""

Do Until Worksheets("DataToCopy").Range("A" & intRow).Value = ""

timesToDuplicate = Worksheets("DataToCopy").Range("A" & intRow).Value
myText = Worksheets("DataToCopy").Range("B" & intRow).Value

For c_Counter = 1 To timesToDuplicate
Worksheets("Result").Range("A" & counterIndex + c_Counter).Value = myText
Next

counterIndex = counterIndex + c_Counter - 1
intRow = intRow + 1
Loop



End Sub

4 responses

Answer 1 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
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
Answer 2 / 4
Cezza
Aug 7, 2014 at 03:28 PM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
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 

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
Cezza
Aug 20, 2014 at 06:52 AM
Hi Trowa

I will give this ago and see if it works

thank you :)
Answer 3 / 4
Cezza
Aug 20, 2014 at 07:08 AM
how do I attach a copy of the spread sheet? I think this would make it clearer :)
Answer 4 / 4
Ceri88 Posts 2 Registration date Thursday August 7, 2014 Status Member Last seen August 20, 2014
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 Contributor Last seen December 27, 2022 555
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: 
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

Similar discussions

Automatically update a cell with last modified date and time
NazCarr -
NazCarr -

2 responses
macro to compare two excel sheets
annajins -
Aks -

6 responses
Automatically Run Macro from a calculated cell value change
smiller -
TrowaD -

1 response