Populating Cells from different tabs in Excel

I've added VLOOKUP to the RF number cell and part number.

I've formatted the data as a Table to make the formula easier & more dynamic.

If you need further help let me know

Hi Luis,

Hope you would have just 3 Requisition Numbers as per RFQ.
And Requisition Number cells are fixed.

If yes then attached file will do what you are looking for...

I have create NamedRange for all the sheets called SuppliersList.

For PartNumber add below Array formula confirm with Ctrl+Shift+Enter in D10:

=IFERROR(VLOOKUP($B$10,INDIRECT("'"&INDEX(SuppliersList,MATCH(1,--(COUNTIF(INDIRECT("'"&SuppliersList&"'!$B:$N"),$B$10)>0),0))&"'!$B:$N"),2,FALSE),"")

Select allOpen in new window

And so on, I have entered formula in every required field referencing Requisition Number
For getting Requisition Number as per RFQ numbers, I have below code:

Sub UpdateReqNum()
Dim SummSh As Worksheet, Ws As Worksheet
Dim LR As Long
Dim SearchRow As Integer, CopyToRow

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

'Set Summary Sheet
Set SummSh = Worksheets("SUMMARY")

'Clear Old Records
SummSh.Range("B10").ClearContents
SummSh.Range("B21").ClearContents
SummSh.Range("B32").ClearContents

'Start Pasting Data to 10th Row
CopyToRow = 10

'Search RFQ Number in Multiple Sheets and copy data to Summary Sheet
For Each Ws In ThisWorkbook.Worksheets
    LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
    For SearchRow = 2 To LR
        If Ws.Range("A" & CStr(SearchRow)).Value = SummSh.Range("B3").Value Then
            Ws.Range("B" & CStr(SearchRow)).Copy
            SummSh.Range("B" & CStr(CopyToRow)).PasteSpecial xlPasteValues
            CopyToRow = CopyToRow + 11
            Application.CutCopyMode = False
        End If
    Next SearchRow
Next Ws

'Enable Events
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Select allOpen in new window

Kindly note, your cell format for RFQ Number in all other sheets and Summary sheet is different. I already changed it.

In attached first change the RFQ number in B3, then click Get Req Numbers. That's it...

Hope this helps.
C-Users-Luis.Freund.EXT-Desktop-v1.xlsm

There could be upwards to 10 but no more than that.

Luis,

You can have as many Requisition Number you want, obviously you gonna copy paste manually Requisition details as it extends as in below image. Right?

Here you go...

I have extended up to 10...
C-Users-Luis.Freund.EXT-Desktop-v2.xlsm

Thanks....works great....but I've added some additional changes in the sheet itself and when I tried to do what you did of course for me it doesn't work.

I've attached the revised sheet to account for the additional changes....sorry about this.
C--Users-Luis.Freund.EXT-Desktop-RF.xlsm

Why are you using VBA for this, seems like overkill to me?

Luis,

As I mentioned earlier, your cell format is not matching with B3 in Summary Sheet, if you type manually RFQ number in Supplier Sheet and press Get Req Numbers, it works
C-Users-Luis.Freund.EXT-Desktop-v3.xlsm

My example works fine and all it required was changing the cell formats manually, not VBA!!

Hi Shums....

Yes it works great but I added Supplier No. and Sole Source to the form.  That's the addition.....

Can we add that too?

Roy...I don't see the example

I'm not sure what's happened there,

Here's the example

Here's a link to get you started understanding Tables
C--Users-Luis.Freund.EXT-Desktop-Lu.xlsm

Thank you very much Shums!

You're Welcome Luis! Glad I was able to help