LOOKUP Across Worksheets and Paste Data?

[ScotyB]

Hi all,

in the attached workbook there are two sheets, one called "Transcribed" which pulls in data from another workbook (the code for which was sorted out for me here at the forums, thanks) - this worksheet contains the full range of data for specific items. My boss, however, being a fussy person, wants a summary page that outlines the number of times a specific event happens and some summary details on the sheet called "Report Page". The numbers thing is easy enough - I've done some countifs for that. It's the next bit I'm having trouble with and I've highlighted a segment in pink on the Report Page to show what I'd like. It goes like this....

User clicks the command button just to the right of the countif number and the vba code will do the following:

1. If the number is zero, delete the numbered rows (in this case, 1 - 20) and the sub-heading just above it,
2. If the number is greater than 20, it will lookup the sub-headings details on the "Transcribed" sheet and paste in ONLY the first 20 records it finds,
3. If it's less than 20, lookup the required details, paste them in THEN delete any empty rows.

I tried a couple of variations on the differing lookup formulas but got myself into strife real quick.

I'm hoping that this is possible and once I can see how it's done for that particular area, I can then modify the code to suit the other couple of areas where I have numbered rows for other events.

Cheers,

AJ

[ScotyB]

Just bumping this up in case anyone has had any ideas,

cheers,

AJ

[ScotyB]

*one more bump* and then I might have to look at a different way of doing this, maybe some pivot tables...

[kvsrinivasamurthy]

We can use formulas in the reqrired sheet, if all meging cells are removed.

[ScotyB]

Yes, I thought that might be a problem since I got a few errors regarding cell sizes when I was experimenting with copy/paste macros - I can fiddle around with the format of the page again to remove merging - so, do you have an idea for a formula in cells that I can drag and fill or are you talking code?

Thanks for responding, kvs, I appreciate it.

AJ

[kvsrinivasamurthy]

Pl attach file after removing Merged cells.

[ScotyB]

Thanks, kvs,

have removed cell merging in the affected areas - file reattached.

Cheers,

AJ

[ScotyB]

*one last bump*