URGENT - Duplication Process

[t5timesb]

I need help with creating the following operation:

Finished Good 1	0	A
Finished Good 1	1	B
...	...	...
Finished Good 1	20	U
Finished Good 2	0	A
Finished Good 2	1	B
...	...	...
Finished Good 2	20	U
Finished Good 30,888	0	A
Finished Good 30,888	1	B
...	...	...
Finished Good 30,888	20	U

<tbody>
</tbody>

Column A is duplicated and then changed to the next in order. Column B & C remain the same. Column B stays 1-20. Column C is the same for the 20 rows, but is not actual "A", "B"..."U".

 

[Andrew Poulsom]

What have you tried and why is it URGENT?

 

[t5timesb]

I have posted below what I have tried.

Here is why it isn't working for me:
1) I already have my Column A codes defined and the code below rewrites Column A by changing the last number in the code. Column A cannot be changed, but must duplicate itself 20 times to match the rows in column B & C. After it is duplicated 20 times, it would go to the next finished good code.
2) Column C above is just an example with text "A-U", I have other text that will simply just need to be copied.

URGENT as I have been working on this for 2 days and am getting no where and I have to have it turned in by tonight.

Sub FinishedGoods() 'with the sheet of imported data active... do the following Macro.
Application.ScreenUpdating = False
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
FGRows = lastRow * 21
ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Finished Goods" ' named Finished Goods
ActiveCell.FormulaR1C1 = "=INDIRECT(""Sheet1!A""&INT(ROW(R[20]C)/21))"
Range("B1").FormulaR1C1 = "=MOD(ROW()+20,21)"
Range("C1").FormulaR1C1 = "=CHAR(RC[-1]+65)"
Range("A1:C1").AutoFill Destination:=Range("A1:C" & FGRows)
Range("A1:C" & FGRows).Copy
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:A").EntireColumn.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

Can you post an example of what's in column A on Sheet1?

 

[t5timesb]

FW15RH20.KBSTVS.GDRB60

FW15RH20.KBSTVS.GDBW60

FW15RH20.KBSTVS.GDRB58

FW15RH20.KBSTVS.GTVB6R

FW15RH20.KBSTVS.GTVB6B

FW15RH20.KBSTVS.GTVB5R

FW15RH20.KBSTVS.GTVBC6

FW15RH20.KBSTVS.GTWB60

FW15RH20.KBSTVS.GTWW60

FW15RH20.KBSTVS.GTWR60

<colgroup><col></colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

Instead of:

ActiveCell.FormulaR1C1 = "=INDIRECT(""Sheet1!A""&INT(ROW(R[20]C)/21))"

try:

ActiveCell.FormulaR1C1 = "=INDEX(Sheet1!C,INT((ROW()-ROW(R1C))/21)+1)"

 

[t5timesb]

It didn't work. It still changed the finished goods text.

What about this, I can copy columns B & C down with a simple drag, but what about copying the Column A, each row 20 times down. This code does the trick, except it only copies A1, I want it to do all of Column A...what do I change?

Sub test()
Dim x AsInteger
x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
If x = FalseThenExitSub
ActiveCell.EntireRow.Copy
Range(ActiveCell.Offset(1), ActiveCell.Offset(x)).EntireRow.Insert
Application.CutCopyMode = False
EndSub

 

[Andrew Poulsom]

What do you mean by "it still changed the finished goods text"? That formula repeats each item in your list 21 times.

 

[t5timesb]

The first finished good "FW15RH20.KBSTVS.GDRB60" becomes:

FW15RH20.KBSTVS.GDRB60

FW15RH20.KBSTVS.GDRB61

FW15RH20.KBSTVS.GDRB62

FW15RH20.KBSTVS.GDRB63

FW15RH20.KBSTVS.GDRB64

FW15RH20.KBSTVS.GDRB65

FW15RH20.KBSTVS.GDRB66

FW15RH20.KBSTVS.GDRB67

FW15RH20.KBSTVS.GDRB68

FW15RH20.KBSTVS.GDRB69

FW15RH20.KBSTVS.GDRB70

<colgroup><col></colgroup><tbody>
</tbody>

and Column B & C are not copied.

However, do you know how to change the range from A1 to the full column A and this would work:

Sub test()
Dim x AsInteger
x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
If x = FalseThenExitSub
ActiveCell.EntireRow.Copy
Range(ActiveCell.Offset(1), ActiveCell.Offset(x)).EntireRow.Insert
Application.CutCopyMode = False
EndSub

 

[Andrew Poulsom]

For me FW15RH20.KBSTVS.GDRB60 is repeated 21 times. Isn't that what you want?