Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: URGENT - Duplication Process

  1. #1
    New Member
    Join Date
    Dec 2014
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default URGENT - Duplication Process

    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

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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: URGENT - Duplication Process

    What have you tried and why is it URGENT?
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    Dec 2014
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: URGENT - Duplication Process

    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

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: URGENT - Duplication Process

    Can you post an example of what's in column A on Sheet1?
    Microsoft MVP - Excel

  5. #5
    New Member
    Join Date
    Dec 2014
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: URGENT - Duplication Process

    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

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: URGENT - Duplication Process

    Instead of:

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

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

  7. #7
    New Member
    Join Date
    Dec 2014
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: URGENT - Duplication Process

    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

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: URGENT - Duplication Process

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

  9. #9
    New Member
    Join Date
    Dec 2014
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: URGENT - Duplication Process

    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

    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

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: URGENT - Duplication Process

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •