Pulling through certain data from many sheets to a master sheet.

[Double99]

Can someone please help me.

I'm new to Excel and I am learning about Formulas but I know nothing about VBA and Macros.

I am creating a annual leave request spread sheet where users input there there requests for a wanted leave on the desired week of the year. I need to somehow perform the function (detailed below) for auditing purposes and wondered if someone could help me.

I have 53 identical worksheets (the only thing that is different is the name of the worksheet and the date - as each worksheet represents a week in the year).

I also have a 54th sheet called 'MASTER', which I would like to use to copy and paste certain data from sheets 1-53.

I would like to create a button on the MASTER sheet (Using VBA or Macro, sorry I don't know which it is) that performs the following tasks when pressed.

1. Clear all data from cells A5 to P10000 (this is important as everytime it is pressed it will copy paste updaeted data from sheets 1 to 53)... then

2. Look at the data entered from sheets 1 to 53 and copy paste rows (A7 to P300) from all sheets, but only those rows with the letter 'x' in the column 'P' from all sheets (*This is important for reasons listed below.). (Note: The headings for columns A to P for sheets 1-53 and the Master sheet are identical in every way).

3. When data is displayed on the MASTER Sheet, it needs to be sorted by column 'A' (a text column), then column 'B' (a text column), then column 'E' (a date column).

*The letter 'x' column P on sheets 1 to 53 denotes a confirmed booking for that week, if it doesn't have the letter 'x' in column P, then a request has been made but it can not be confirmed at this stage, therefore should not be added to the MASTER sheet.

One last thing I should mention is that not all cells in each row being copied and pasted across contain data, some may be blank as an individual may not reqeust leave for that particular day/cell. (I'm not sure if that makes a difference to the coding).

Thankyou for taking time to look at this. I would greatly appricaate anyone who could help me with this request.

Cris

[karan]

Why don't you attach a sample workbook? I will try to make a macro for it. Make sure you remove all the confidential data from it.

If it is larger than 1000 kb, zip it and attach.

--Karan--

[rylo]

Hi

see if the attached gives you some ideas. Note that you must have something in Master!A4. If row 4 is not used for headings, please let us know.

rylo

[Double99]

Rylo, if I sent you the excel file could you please insert the button for me. It would mean so much if you could. Its a large file so I would have to zip it and send it via email. If you are willing to help please PM me a suitable email address.

I'm very sorry I just don't know how to to command buttons and macros

Many Thanks

Double99

(aka Cristian)

[Double99]

Rylo,

I managed to place your sheet onto my spreadsheet. The button works great! but I have one problem for some reason its duplicated the results twice. and I'm not sure why

It displays all 'x' entries made on sheets 1 to 53, but the does it again below. .. so instead of having 500 results (for 500 entries made on sheets 1-53, it displays 1000)

Any ideas?

[karan]

Double99,

I hope you got the file by email. Or shall I attach it here too?

--Karan--