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".

 

[t5timesb]

That is what I want, yes, but I also want all 30,888 rows to be duplicated 20 times.

Basically at the end, I will have 617,760 rows.

 

[Andrew Poulsom]

Please post your current code, after the change I suggested.

 

[t5timesb]

Sub test()
Dim x As Integer
x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
If x = False Then Exit Sub
ActiveCell.EntireRow.Copy
myRange = ActiveSheet.Columns(A)
Application.CutCopyMode = False
End Sub

I need it to copy each row in Column A, not just A1.

 

[Andrew Poulsom]

No, your original procedure as modified.

 

[t5timesb]

If I could change the range above, I can fix it though.

Here is what you are asking for:
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 = “=INDEX(Sheet1!C,INT((Row()-ROW(R1C))/21)+1)”
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]

Your code worked for me:

Sub FinishedGoods() 'with the sheet of imported data active... do the following Macro.
    Dim LastRow As Long
    Dim FGRows As Long
    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 = "=INDEX(Sheet1!C,INT((Row()-ROW(R1C))/21)+1)"
    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

 

[t5timesb]

To confirm "it worked":

1) You have 617,760 lines?
2) Each "finished good" in column A is copied 20 times creating the 617,760 lines?

I tried again and in my first 10 lines on, I got (notice how the 60 goes to 61 to 62):

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>

Is there a way you can send me your file?

 

[Andrew Poulsom]

With your sample data from Post #5 I got 210 rows with each item repeated 21 times. Here are the first 22 rows:

Excel 2010
	A	B	C
1	FW15RH20.KBSTVS.GDRB60	0	A
2	FW15RH20.KBSTVS.GDRB60	1	B
3	FW15RH20.KBSTVS.GDRB60	2	C
4	FW15RH20.KBSTVS.GDRB60	3	D
5	FW15RH20.KBSTVS.GDRB60	4	E
6	FW15RH20.KBSTVS.GDRB60	5	F
7	FW15RH20.KBSTVS.GDRB60	6	G
8	FW15RH20.KBSTVS.GDRB60	7	H
9	FW15RH20.KBSTVS.GDRB60	8	I
10	FW15RH20.KBSTVS.GDRB60	9	J
11	FW15RH20.KBSTVS.GDRB60	10	K
12	FW15RH20.KBSTVS.GDRB60	11	L
13	FW15RH20.KBSTVS.GDRB60	12	M
14	FW15RH20.KBSTVS.GDRB60	13	N
15	FW15RH20.KBSTVS.GDRB60	14	O
16	FW15RH20.KBSTVS.GDRB60	15	P
17	FW15RH20.KBSTVS.GDRB60	16	Q
18	FW15RH20.KBSTVS.GDRB60	17	R
19	FW15RH20.KBSTVS.GDRB60	18	S
20	FW15RH20.KBSTVS.GDRB60	19	T
21	FW15RH20.KBSTVS.GDRB60	20	U
22	FW15RH20.KBSTVS.GDBW60	0	A
Finished Goods

 

[Gilliam]

@t5timesb as per your request on the other thread...

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 'if the numbers go fromm 0-20 that's 21 numbers...
    ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count) 'I'll just put everything in a new worksheet
    ActiveSheet.Name = "Finished Goods" ' named Finished Goods
    ActiveCell.FormulaR1C1 = "=INDEX(Sheet1!C,INT((Row()-ROW(R1C))/21)+1)" 'this will be VERY SLOW
    Range("B1").FormulaR1C1 = "=MOD(ROW()+20,21)" 'this gets the numbers 0-20 in column B
    Range("C1").FormulaR1C1 = "=VLOOKUP(CHAR(RC[-1]+65),{""A"",""CUTOFF"";""B"",""SHAFT1"";""C"",""FERRULE"";""D"",""FERRULEFW15"";""E"",""GRIP"";""F"",""GRIP1"";""G"",""MATCHPOINT"";""H"",""CLUBHEAD"";""I"",""ASSEMBLY"";""J"",""GRINDFERRULES"";""K"",""LOFT/LIE"";""L"",""LASER"";""M"",""CLEAN"";""N"",""BAND"";""O"",""ISLABEL"";""P"",""PACK"";""Q"",""BXSET15"";""R"",""INCFREIGHT"";""S"",""DIRECTLA"";""T"",""VARIABLEOH"";""U"",""FIXEDOH""},2,TRUE)" 'this gets the letters A thru U in column C
    Range("A1:C1").AutoFill Destination:=Range("A1:C" & FGRows) 'because it's slow, I will paste over itself with just the values.
    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

 

[t5timesb]

I have tried on 3 different desktops and it crashes each time. Any suggestions.