Automating Excel Spreadsheet

W

wallace23

New Member
Joined
Aug 25, 2011
Messages
15
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. :)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Formula that will Look for a value in a table column that is on another worksheet...
Replies
5
Views
364
sspatriots
S
H
  • Question Question
List out duplicate items in a list with different costs/values
Replies
6
Views
170
H3MSY
H
J
  • Question Question
Multiple Layer VLOOKUP, or better alternative?
Replies
9
Views
625
JukeExcel
J
S
  • Question Question
Help with a vlookup formula
Replies
6
Views
587
Bosquedeguate
Bosquedeguate
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
953
Alex Blakenburg
A

Forum statistics

Threads
1,224,587
Messages
6,179,740
Members
452,940
Latest member
Lawrenceiow

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top