macro to copy rows that are highlighted to another sheet 2

[joseph_pat]

Hi

Was hoping someone could point me in the right direction

I have a spreadsheet that has a list of dates, names and Rooms (7 coloums).
At the moment i have a Cell in which i would type in Room 1 and it would highlight all the cells that have Room 1 in it (conditional Formatting)
Then i would open Sheet 1 and sheet 2 side by side and 'copy' & 'paste value' to sort each list into sorted lists.

Been stuck on creating a macro button that could do this copy the row which has a highligted cell and paste it into the cells i want it to.

Any help would be much appreciated

thankyou

[mikeTRON]

Why bother with VBA when you can make a pivot table that will automatically grab what you need and you can filter on the room number?

If you are trying to create little reports on each sheet for a different room number, then create a pivot on a sheet with a room filter, then copy the sheet and change the filter.
Then when you add data, you can have the pivots refreshed and it flows to all of them without any VBA.

[joseph_pat]

Unfortunately i have considered that option and would have to stick to macros - the data then needs to be added to and modified - hence only a macro would be ideal

thankyou for the input

[mikeTRON]

How is your data so unique that you can't use a pivot table?

[nimrod]

Sheet1 h1 = "Room"
Sheet1 h2 = The Room eg. Rm 1

When H2 is changed the following code is triggered (NOTE: code must be copied into Sheet1's code window

EXPLAINATION: This macro utilizes excels "advanced filter" feature. It utilizes H1:H2 as the criteria to filter on room and then copies the filtered information to sheet2

Please Login or Register  to view this content.