Referencing Form One Worsheet Into Another - Visible Cells Only

[element2013]

I've been searching for something that might help me on this without any luck. I have a bunch of data in a worksheet I call "Planning". It has about 10 columns and more than 100 rows and expanding. This is where I do all filtering depending on the report needed.

I want to create a separate worksheet that references all visible data from "Planning" into a "Task Monitoring worksheet". I want this "Task Monitoring" worksheet to have only visible rows as is and not the hidden information being filtered.

Is this possible?

[HaHoBe]

Hi, element2013,

if you set up your worksheet with headers, a criteria range and the heading for the output you could use the Advanced Filter without VBA (headings must be identical for the data set in all three ranges).

The other way might be to use the Worksheet_Activate-event for the sheet Task Monitoring worksheet, right-click on that tab and choose View code, code goes into code window which opens:

Please Login or Register  to view this content.

If you data doesnīt start in A1 please adjust (as well as the name of the worksheet if needed).

Ciao,
Holger

[element2013]

This is what I have so far. I used the code from HaHoBe and it works. I have this other tab called "Reporting" (See Attachment). All the outstanding tasks populate from the "Task Monitoring" tab which feeds from "Planning." However, whenever I filter individual task owners or Project Tasks, that information does not populate into the "Reporting" tab.

Any ideas on why it is doing this?

[HaHoBe]

Hi, element2013,

get the last visible row on Sheet Task Monitoring" in Column B, build a range on that information and copy the data (the copying is not included here but Iīm sure you can work that out on your own):

Please Login or Register  to view this content.

Ciao,
Holger

[element2013]

I'm still getting the same problem. I'm not sure if I understand how to use the second code. Keep in mind that I will constantly be adding data to the "Planning" sheet. The "Task Monitoring" sheet will always be different depending on what I filter.

Thanks in advance for any additional help with this problem!

[HaHoBe]

Hi, element2013,

the fitering from Planning to Task Monitoring should do okay but Iīm sorry I think I didnīt get the requirement for the data transfer from Planning to Reporting correctly. Do you want the fitered data from Planning to be shown or any new data which isnīt present in Reporting? I suggested the code because I thought that the data to be transferred isnīt within a continuous range (maybe Iīve got that all wrong,cCould you please clarify for me? Thanks).

Ciao,
Holger