Welcome to the forum MSRudy,
What I would suggest doing is creating a macro to do that for you. Below I have created a mock row duplicator with information on how I created it so you can pick it apart and try it yourself.
I used your formula you provided. It works by selection. Select the rows you want to duplicate with the columns of " Item Number | AMOUNT | " and any other columns you want to duplicate. The second column selected has to be the Amount.
The macro will create a new sheet and populate your data.
Code:
Sub DuplicateRows()
'this works by selection. Select the rows you want to duplication and
'have them formated in ROW NUMBER - HOW MANY ROWS YOU WANT
'sets all integers we will use to count
Dim col As Integer
Dim ro As Integer
Dim colCount As Long
Dim roCount As Long
Dim NM As String
Dim PasteRow As Long
'gathers information on the selected cells
col = Selection.Column
ro = Selection.Row
colCount = Selection.Columns.Count
roCount = Selection.Rows.Count
'checks to see if the column count is greater than 1 - if it is not then it will not run
If colCount <= 1 Then
MsgBox ("Please select a COLUMN ITEM - COLUMN DUPLICATION - and the rest of the data you want to copy. A minimum of 2 columns must be selected")
Exit Sub
End If
'this hides all processes so you will not see them execute - it will also make it run faster
Application.ScreenUpdating = False
'sets the sheets and creates a new sheet
Dim sh As Worksheet
Dim sh2 As Worksheet
Set sh = ActiveSheet
Sheets.Add After:=ActiveSheet
Set sh2 = ActiveSheet
'specify what row to start on the new sheet
PasteRow = 1
'begin a loop to go through all the selected rows
For R = ro To (roCount + ro - 1)
NM = sh.Cells(R, col).Value
If NM = "" Then
Else
For i = 1 To sh.Cells(R, col + 1).Value
'copy and paste data
sh.Range(Cells(R, col).Address, Cells(R, colCount).Address).Copy
sh2.Cells(PasteRow, 1).PasteSpecial xlPasteValues
'add to pasterow so we will not overwrite data
PasteRow = PasteRow + 1
Next
End If
Next
sh2.Activate
'make user see changes
Application.ScreenUpdating = True
MsgBox ("Process is completed")
End Sub
some useful sites to look at to better understand these codes can be found below:
Looping -
Selection -
Copy/Paste
Thank you so much!
Works like a charm!
Sorry tonny i havent tried your method since its already working.
I have got a small other question though:
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array("10780", "10782", "10783", "10784", "10785", "10786", "10787", "10789", "10790", "10791", "10792", "10793", "10794", "10795", "10796", "10797", "10798", "10799", "10800", "10801", "10802", "10803", "10804", "10805", "10806")
rplcList = Array("90310011", "90310012", "90310020", "90310023", "90310039", "90310044", "90310051", "90310054", "90310061", "90310066", "90310079", "90310096", "90310099", "90310100", "90310101", "90310113", "90310119", "90310143", "90310148", "90310150", "90310154", "90310159", "90310176", "90310177", "90310161")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
This is a macro that replaces the fndList with whats in the rplcList. However this is a macro that works for the entire document. I only want it to work on column G.
Cant be too hard i think but i cant find how to do it. Ive got a lot of other stuff working today, this stuff is fun too
