asked on

Excel VB to Loop a copy and paste based upon a cell value

I have a sheet called "main" that has the following values in cells starting at cell B5. B5 has value of ItemNumber and cell C5 is HCI-1111, etc.

ItemNumber      HCI-1111
Warehouse      HCI
OrderNumber 99999
Line Number      1
TransactionDate      3/31/2009
TransactionTime      9:46:34 AM
Quantity      1
Shipped      TRUE
SelectForShip      FALSE
PrintDate      3/31/2009
Username      CFLAXM

In a second sheet named "createData" I have the 1st row with the following headings in Column A thru T.

ItemNumber      ItemFiller      Warehouse      OrderNumber      RMANumber      Source      ControlNumber      Line Number      LevelNumber      SequenceNumber      BINNumber      TransactionDate      TransactionTime      Quantity      Cost      MfgrSeriallot      Shipped      SelectForShip      PrintDate      Username

I am looking for help in creating a VB macro that will copy the data on the "Main" sheet Column "C" to the "createData" sheet placing the data in the corresponding columns.

Now I want to be able to control the loop by a cell value, so if I need 100 rows copied with the same data I would enter the value 100 into Cell F5 on "main" sheet.

I know nothing about VB, I would need the script written.

nutsch

Here is a code that will work if your title row in sheets createdata is in row 1.

Here is also the sample file I ran it on.

HTH

Thomas

Option Explicit
 
Sub Macro1()
Dim orgSht As Worksheet, destSht As Worksheet
Dim i As Long, lastRow As Long, rowIndex As Long
Dim j As Integer, lastCol As Integer
 
Application.ScreenUpdating = False
 
Set orgSht = Sheets("main")
Set destSht = Sheets("createData")
 
rowIndex = 1
 
lastRow = orgSht.Range("B65536").End(xlUp).Row
lastCol = destSht.Range("IV1").End(xlToLeft).Column
 
For i = 5 To lastRow
    If orgSht.Cells(i, 2) = "ItemNumber" Then
        rowIndex = rowIndex + 1
        destSht.Cells(rowIndex, 1) = orgSht.Cells(i, 3)
    Else
        For j = 2 To lastCol
            If destSht.Cells(1, j) = orgSht.Cells(i, 2) Then
                destSht.Cells(rowIndex, j) = orgSht.Cells(i, 3)
                GoTo nxtI
            End If
        Next
    End If
nxtI:
Next
 
Application.ScreenUpdating = True
End Sub

Open in new window

24281552.xls

Researcher007

ASKER

nutsch- your code works great, it placed the data in the correct column. The only thing it doesn't do is give me an option to replicate the data. Perhaps this is not possible. I need to copy that same data 100 times or however many times, it varies. As it is now I would need to click a button the number of replications I need. It would be nice to have a cell control the number of replications. Perferrably a cell on the "main" sheet.

nutsch

you mean you just have one item number and want to copy that line x times
OR
you have multiple item numbers and want to copy each x times.

Researcher007

ASKER

One item number and want to copy that line x times, sorry if I didn't do a good job explaining that.

Researcher007

ASKER

Nutsch- are you still working on this or do I need to look at another option?

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial