I am trying to write a macro that will select rows from "sheet 1" and
depending on the value in column "BU" copy the row to sheets 2, 3 or 4
given the following condition.

If the cell in column BU = 1 then copy to sheet 2; I
f the cell in column BU = 2 then copy to sheet 3;
If the cell in column BU = 3 then copy to sheet 4

The copied rows need to be inserted in sheets 2,3 & 4 from row 13 down
to account for the header.

The detrmining number in BU is set using and IF statement in excel, I
assume this shouldn't have any impact on the macro code required?

I have tried a number of differnt approaches but no joy as yet which
is no doubt not helped by my lack of experience with VB.

Any help would be gratefully appreciated as would any comments on the
code suggested to help understand the procedures.

Many Thanks

Jamie

You didn't say what row of BU. Try this, supplying the intended rows.

Sheets("Sheet1").Rows("r1:r2").Copy _
Destination:= Sheets("Sheet" & 1 + Sheets("Sheet1").Cells(r3,
"BU")).Rows("13")

Hth,
Merjet

Thanks for the suggestion. I dont think I was very clear about what I
was trying to do. I need to search the cells in column BU down to the
end of the data and copy the rows to seperate sheets depending on the
value in BU. The thing Im really struggling with is how to implement
3 seprate "If" conditions.

If the value in cell BU?? is 1, I need to copy the entire row to sheet
2. If the value in cell BU?? is 2, I need to copy to sheet 3 and
finally if BU?? = 3 I need to copy the entire row to sheet 4. (I have
put the ?? in to represent different row numbers). My header is 12
rows so the data is from row13 downwards. The headers on sheets 2 , 3
and 4 are tghe same.

Thanks

Jamie

My code does exactly what you say. The value of 1 +
Sheets("Sheet1").Cells(r3,
"BU") will vary depending on what is in column BU. If it = 1, Sheet2
will be used. If it = 2, Sheet3 will be used, etc. If you want to
copy from multiple rows, then put my code within a loop.

Hth,
Merjet