Code To Copy Highlighted Row Insert & Add A, B etc On End Of Data In C

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,748
Office Version
  1. 365
Platform
  1. Windows
I need a code that when I highlight a row(s) it copies and inserts the row beneath and adds A & B to the end of data in C.

In the sample below if I highlight all three rows the data in C is different in C so I will need a total of 6 rows with A & B on the end in as the result

Excel Workbook
ABCDEFG
12MZC1820 2001datadatadatadata
13MZC1820 3001datadatadatadata
14MZC1820 4001datadatadatadata
Sheet1


Result

Excel Workbook
ABCDEFGHIJ
12MZC1820 2001Adatadatadatadatadatadatadata
13MZC1820 2001Bdatadatadatadatadatadatadata
14MZC1820 3001Adatadatadatadatadatadatadata
15MZC1820 3001Bdatadatadatadatadatadatadata
16MZC1820 4001Adatadatadatadatadatadatadata
17MZC1820 4001Bdatadatadatadatadatadatadata
Sheet1



Thanks.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
I have the code below that does a similar thing apart from adding the A's etc on the end.

I highlight the rows I want copied and inserted and a pop up box asks me how many times I want it copied and inserted. Could it be amended so that if I select 3 times then it puts A-D at the end of the data in C on the 4 rows or if i select 2 then A, B & C is added, if I just select 1 then A is added to the existing row and B onto the new row and so on...

Code:
Sub CopyAndInsertRow()
'Copies selected rows and inserts amount you want entered in input box, then sorts by column C.
    Set mySource = Selection.EntireRow
mySource.Copy
selectedrowscount = mySource.Rows.Count
Dim X As Long, Y As Long
X = mySource.Row
Dim t
t = InputBox("Enter Number Of Times Selected Row(s) To Be Copied.", "Number of Copies", 1)
If t = 0 Or t = "" Then Exit Sub
Y = X + t * selectedrowscount - 1
Rows(X & ":" & Y).Insert Shift:=xlDown
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Rows(X & ":" & Y + selectedrowscount)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Application.CutCopyMode = False
End Sub
 
Last edited:
Upvote 0
Hi
Please test this. Do you want the result sorted?

Code:
Sub CopyAndInsertRow()
Dim src As Range, x&, y&, t, cnt%, i%, j%, lr%, chc%, rv
    
Set src = Selection.EntireRow
t = InputBox("Enter Number Of Times Selected Row(s) To Be Copied.", "Number of Copies", 1)
If t = 0 Or t = "" Then Exit Sub
For i = 1 To src.Rows.Count
    chc = 65                ' A
    lr = Range("c" & Rows.Count).End(xlUp).Row + 1
    src.Cells(i, 3).Value = src.Cells(i, 3).Value & Chr(chc)
    src.Rows(i).Copy
    For j = 0 To t - 1
        chc = chc + 1
        Range("c" & (lr + j)).EntireRow.PasteSpecial xlPasteAll
        rv = Range("c" & (lr + j)).Value
        Range("c" & (lr + j)).Value = Left(rv, Len(rv) - 1) & Chr(chc)
    Next
Next


Application.CutCopyMode = False
End Sub
 
Upvote 0
Yes please sorted by column c. When I tried it an error came up saying 'overflow'?
 
Last edited:
Upvote 0
Hi.. try this..

I wasn't 100% sure about this:
if I select 3 times then it puts A-D at the end of the data in C on the 4 rows or if i select 2 then A, B & C is added, if I just select 1 then A is added to the existing row and B onto the new row and so on...

Note:

I just adapted Mr Worfs code.. in this case.. select the required cells and press CommandButton1..

Code:
Private Sub CommandButton1_Click()
Dim src As Range, x&, y&, t, cnt%, i%, j%, lr%, chc%, rv
    
Set src = Selection.EntireRow
t = InputBox("Enter Number Of Times Selected Row(s) To Be Copied.", "Number of Copies", 1)
If t = 0 Or t = "" Then Exit Sub
For i = 1 To src.Rows.Count
    chc = 65                ' A
    lr = Range("c" & Rows.Count).End(xlUp).Row + 1
    
    src.Rows(i).Copy
    
    Select Case t
    Case 1
    src.Cells(i, 3).Value = src.Cells(i, 3).Value & Chr(chc)
     For j = 0 To t - 1
        chc = chc + 1
        Range("c" & (lr)).EntireRow.PasteSpecial xlPasteAll
        rv = Range("c" & (lr)).Value
        Range("c" & (lr + j)).Value = Left(rv, Len(rv) - 1) & Chr(chc)
    Next
    
    Case 2
    Range("c" & (lr)).EntireRow.PasteSpecial xlPasteAll
        rv = Range("c" & (lr)).Value
        Range("c" & (lr + j)).Value = Left(rv, Len(rv)) & Join(Array(Chr(65), Chr(66), Chr(67)), "")
    
    Case 3
        Range("c" & (lr)).EntireRow.PasteSpecial xlPasteAll
        rv = Range("c" & (lr)).Value
        Range("c" & (lr + j)).Value = Left(rv, Len(rv)) & Join(Array(Chr(65), Chr(66), Chr(67), Chr(68)), "")
    End Select
Next


Range("C2").CurrentRegion.Sort _
      key1:=Range("C1"), order1:=xlAscending, Header:=xlYes


Application.CutCopyMode = False
End Sub

Initial Data: To test - select C2:G4
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:135px;" /><col style="width:64px;" /><col style="width:140px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td >Header1</td><td >Header2</td><td >Header3</td><td >Header4</td><td >Header5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td >MZC1820 2001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td >MZC1820 3001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td >MZC1820 4001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

After 1 is Selected in InputBox:
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:135px;" /><col style="width:64px;" /><col style="width:140px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td >Header1</td><td >Header2</td><td >Header3</td><td >Header4</td><td >Header5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td >MZC1820 2001A</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td >MZC1820 2001B</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td >MZC1820 3001A</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td >MZC1820 3001B</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td >MZC1820 4001A</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td >MZC1820 4001B</td><td >data</td><td >data</td><td >data</td><td >data</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

After 2 is Selected in InputBox:
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:135px;" /><col style="width:64px;" /><col style="width:140px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td >Header1</td><td >Header2</td><td >Header3</td><td >Header4</td><td >Header5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td >MZC1820 2001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td >MZC1820 2001ABC</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td >MZC1820 3001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td >MZC1820 3001ABC</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td >MZC1820 4001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td >MZC1820 4001ABC</td><td >data</td><td >data</td><td >data</td><td >data</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

After 3 is Selected in InputBox:
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:135px;" /><col style="width:64px;" /><col style="width:140px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Header1</td><td >Header2</td><td >Header3</td><td >Header4</td><td >Header5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >MZC1820 2001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >MZC1820 2001ABCD</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >MZC1820 3001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >MZC1820 3001ABCD</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >MZC1820 4001</td><td >data</td><td >data</td><td >data</td><td >data</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >MZC1820 4001ABCD</td><td >data</td><td >data</td><td >data</td><td >data</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
No sorry, you misunderstand. If 3 is selected in the input box I need the row(s) I have highlighted copied and inserted below 3 times with A, B, C & D at the end of the data in C on each row.

Before

Sheet1

*ABCDEFGH
12DataDataMZC1820 2001DataDataDataDataData
13DataDataMZC1820 3001DataDataDataDataData
14DataDataMZC1820 4001DataDataDataDataData

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 42px"><COL style="WIDTH: 42px"><COL style="WIDTH: 90px"><COL style="WIDTH: 80px"><COL style="WIDTH: 119px"><COL style="WIDTH: 72px"><COL style="WIDTH: 88px"><COL style="WIDTH: 42px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


After selecting 3 in input box

Sheet1

*ABCDEFGH
12DataDataMZC1820 2001ADataDataDataDataData
13DataDataMZC1820 2001BDataDataDataDataData
14DataDataMZC1820 2001CDataDataDataDataData
15DataDataMZC1820 2001DDataDataDataDataData
16DataDataMZC1820 3001ADataDataDataDataData
17DataDataMZC1820 3001BDataDataDataDataData
18DataDataMZC1820 3001CDataDataDataDataData
19DataDataMZC1820 3001DDataDataDataDataData
20DataDataMZC1820 4001ADataDataDataDataData
21DataDataMZC1820 4001BDataDataDataDataData
22DataDataMZC1820 4001CDataDataDataDataData
23DataDataMZC1820 4001DDataDataDataDataData

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 42px"><COL style="WIDTH: 42px"><COL style="WIDTH: 120px"><COL style="WIDTH: 80px"><COL style="WIDTH: 119px"><COL style="WIDTH: 72px"><COL style="WIDTH: 88px"><COL style="WIDTH: 42px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
I tried your code to see what happened anyway apo and i also got the 'overflow' error?
 
Upvote 0
Hi..

I don't get an overflow error.. btw.. I also didn't get an overflow error when i tried Worfs code as it is.. so not sure what's going on there..

This works (modified version after clarification) here and does what you show..

Note again.. most of it is Worfs code.. :)

Code:
 Private Sub CommandButton1_Click()
    Dim src As Range, x&, y&, t, cnt%, i%, j%, lr%, chc%, rv


    Set src = Selection.EntireRow
    t = InputBox("Enter Number Of Times Selected Row(s) To Be Copied.", "Number of Copies", 1)
    If t = 0 Or t = "" Then Exit Sub
    For i = 1 To src.Rows.Count
        chc = 65                ' A
        src.Rows(i).Copy
        src.Cells(i, 3).Value = src.Cells(i, 3).Value & Chr(chc)
        For j = 0 To t - 1
            chc = chc + 1
            lr = Range("c" & Rows.Count).End(xlUp).Row + 1
            Range("c" & (lr)).EntireRow.PasteSpecial xlPasteAll
            rv = Range("c" & (lr)).Value
            Range("c" & (lr)).Value = Left(rv, Len(rv) - 1) & Chr(chc)
        Next
    Next
    Range("C2").CurrentRegion.Sort _
            key1:=Range("C1"), order1:=xlAscending, Header:=xlYes
    Application.CutCopyMode = False
End Sub

Here the Workbook to show you it working:
Insert Row
 
Upvote 0
Your workbook does exactly as I want but when I do it on my files I get the overflow? If it makes a difference the columns stretch over to AZ, and rows down to 50,000 odd, all with data in.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top