Automating Excel Spreadsheet

[wallace23]

Hello All,
New to this site. Need some help with a macro. I dont know if it is possible or I am just approaching the wrong way.
It is a little complex to explain so I hope I explain it correctly and don't confuse anyone.

I have a tab called "July PO Data" and I need certain data (PO #, PO QTY, Unit Price & extended Price) to automatically populate into the "Part II" tab according to Part Numbers.

For example part number 6095533, So i need the PO#, PO Qty, unit Price and extended price in "JULY PO Data" that is under that part number (6095533) needs to populate into "Part II" cell AB9, AC9, AD9 and AE9 and so on. Part number 6095533 has 5 PO's in this example.

However the problem is that this is a variably sized ranged, i.e. next month part 6095533 might have 8 PO's for it.

I hope this is not confusing.
Thanks any questions please ask.

 

[tinyskills13]

How is the data set up? Can you just use the Range.End property to figure out how many PO's there are?

 

[wallace23]

Data is downloaded from Cincom (Downloading tool) each month into the "PO Data" tab, so can't determine how PO's there are each month.

 

[tinyskills13]

Sorry, I meant more how is it formatted? If each part number is a row and the PO's are the last things in the row, then you might use Range.End to figure out how many there are. For example, if your part number is in cell A1, you can use Range("A1").End(xltoright).Select to select the last consecutively used cell in row 1.

 

[tinyskills13]

Sub PO_data()
    Dim lastRow As Integer, myRow As Integer
    Dim partNum As String, month As String
    Dim c As Range
    
        
    month = "JULY PO DATA"
    lastRow = Sheets(month).Range("A2").End(xlDown).Row
    myRow = 7   'row to add value on sheet "Part II"
    
    'check each part number on month sheet
    For Each c In Sheets(month).Range("A3:A" & lastRow)
        If c.Value <> partNum Then 'compare part number to previous part number
            myRow = myRow + 1 'skip row for summary line
        End If
        partNum = c.Value
        myRow = myRow + 1
        
        'fill in data
        Sheets("Part II").Cells(myRow, 11).Value = partNum
        Sheets("Part II").Cells(myRow, 28).Value = Sheets(month).Cells(c.Row, 5).Value
        Sheets("Part II").Cells(myRow, 29).Value = Sheets(month).Cells(c.Row, 3).Value
        Sheets("Part II").Cells(myRow, 30).Value = Sheets(month).Cells(c.Row, 17).Value
    Next
    
    
End Sub

You can take a look at this. It's a start. It doesn't seem like the part numbers line up exactly with the example you sent me. But you might be able to see if this is at least on the track you were looking for.

 

[wallace23]

Thanks, it's a start and something I can work with.

Regards,

Wallace23

 

[wallace23]

Works great! Thanks tinyskills you truly got skills.