copy and paste based on criteria

[ezonemy]

hi i have a workbook which contains 2 sheet.

Sheet 1= this is master sheet
Sheet 2= contains list of item(row 9 till 155), each row is different item, staff required to key in amount for that item at column F. Column I contains "defined name" for each item (e.g A9 is item named Admin, F9 is where amount is keyed in and I9 named as item_1).
Require vba code to perform the below;

a) In sheet 2, hide rows from A9 till A155 based on value in column F. (e.g. if F10 is empty, the whole row need to be hidden)
b) Copy all the UNHIDDEN rows from A9 till A155, including the formats and the defined names in column I
c) In sheet 1, paste the copied data at row 168 onwards. If copied data is more than 1 row, need to insert row after row 168 before pasting. Before paste, need to delete any existing info. (there are calculation at row 170 onwards - cannot delete this rows)


Another VBA code to unhide all the hidden rows in sheet 2.

The columns in sheet 1 & sheet 2 are same (column A till M).

Thank you.

 

[C Moore]

Use record macro and do it manually. To modify the existing info delete you'll have to find whatever row it needs to be, probably using Cells.Find(....