VBA to filterthen copy cells from sheet1 then paste vlaue to sheet2 destinated cells

[tcambridge]

Need your helps please! From the attached sample workbook, I need VBA codes to do the following things please:

1- Filter column K on Sheet1 for a particular BU's name then
2- Copy and paste then filtered name on the Blank space on Sheet2-A1
3- Then copy and paste value as following example:

Copy from Sheet1-A6 then Paste Value to Sheet2-A7
Copy from Sheet1-C6 then Paste Value to Sheet2-B7
Copy from Sheet1-H6 then Paste Value to Sheet2-D7
Copy from Sheet1-I6 then Paste Value to Sheet2-E7
Copy from Sheet1-N6 then Paste Value to Sheet2-F7
Copy from Sheet1-O6 then Paste Value to Sheet2-G7
Copy from Sheet1-P6 then Paste Value to Sheet2-H7

The list will go on and on until Sheet1-BE6 to Sheet2-AW7
on Sheet1 from Column G to BE are all formula
On Sheet2 Column O-Q, AA-AC, AM-AO, AY-BA have formula.


I did the Vlookup formula for sheet2. However, the embedded formula in columns O to Q, etc. gave error (#Value) results. I hope you have a better solution for this.

Thank so much for your helps.