Hi,
Something like this?

Sub AddFormulae()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim ws2rng As Range

Set ws1 = Worksheets("BOM")
Set ws2 = Worksheets("MP")
Set ws2rng = ws2.Range("a5")

ws2.Range("a5:b300").ClearContents
ws2.Range("a5:b300").NumberFormat = "General"

lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
ws2rng.Formula = "=BOM!A" & r
ws2rng.Offset(0, 1).Formula = "=BOM!B" & r
ws2rng.Offset(1, 0).Formula = "=BOM!C" & r
ws2rng.Offset(1, 1).Formula = "=BOM!D" & r
Set ws2rng = ws2rng.Offset(2, 0)
Next r

End Sub

"kuansheng" wrote:

> I'm working on creating worksheet selection from a drop down
> list. The drop down list contain: Mouse, Keyboard, Monitor for example:
>
>
> I have 2 worksheet in the same workbook.
> I have a worksheet called (BOM), one of this worksheet for Mouse,
> another for Keyboard and so on. that contain data in 4 column namely:
> Model, Part number, Part description and Quantity and has about 100
> rows
>
>
> For example if i choose "Mouse" from the drop down list then I want
> cell A5 to fill with "Model number" and cell B5 with "Part number." and
>
> cell A6 with "Part description." and cell B6 with "Quantity"
>
>
> All of this fill-in information would be on another worksheet named
> Master plan(MP).
>
>
> I am using formula that refers to data data on the BOM worksheets like
>
> this: ='BOM'!A2 in cellA5 of MP, and ='BOM'!B2 in B5 of MP, ='BOM'!C2
> in A6 of MP and so on. And i repeat by copying the formulas to cell
> below to extract the data for row 2 of the BOM worksheet. I continue
> until i get all 100 rows.
>
>
> My problem is when i copy this formula to some cell below, it does not
> extract the next row of data from the BOM worksheet. Instead it make
> relative reference to the cell thus returning wrong data.
>
>
> Is there a way of going about to do this. Any help is appriciated.
> Thank you.
>
>
> For example i choose from the drop-down list: mouse
>
>
> BOM worksheetcorresponding to mouse)
> Model Part number Part Des Quantity
> M123 02-1234-12 Screw 1
> M124 02-1235-12 Label 2
>
>
> The data will be fill-in to the respective cell in the MP worksheet
> automatically
>
>
> MP worksheet:
> Model:Fill-in here Part number: Fill-in here
> Part Des:Fill-in here Quantity:Fill-in here
>
>