Getting data from multiple worksheets to a master sheet

Hi SmoothRider,
Welcome to the Forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I am offering you a simple VBA solution to achieve this. I have placed a code on Renewals Sheet module for Worksheet_Activate Event. I have also modified the formula in "Days to Exp" column. To view the code right click on Renewals Sheet Tab --> View Code.

The code will work like this. When you activate the Renewals Sheet to view its contents, the code is executed automatically and extract all the relevant data from all the sheets and when the process is completed, you will get a message "Copied Relevant Data." in the end. So you will never see the Renewals sheet empty unless there is no relevant data to be copied on the Renewals sheet.

Please find the attached sheet to see if this works as per your requirement.

Thanks for the feedback.

The code will run successfully no matter how many employee sheets you have without any problem. You just need to exclude the sheets other than employee sheets from the worksheet loop in the following line of code.......

Please Login or Register  to view this content.

Don't forget to write "And" between all the logical conditions and Then in the last of the logical condition.
Hope this helps.

If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

Thanks again for the reply sktneer I really appreciate it. So I have it setup like this

Please Login or Register  to view this content.

But I am getting a Run-time error '424' Object required error, it says its in this line of code,


If ws.Name <> "Renewals" And ws.Name <> "Drop Down Values" And ws.Name <> "Course Instructors" And ws.Name <> "Staff Qualifications" And ws.Name <> "Technical Support Quals" And we.Name <> "Template Employee Sheet" Then

What have I done wrong?

Make sure that your sheet names as exactly same as listed in the code i.e. no sheet name has any leading or trailing space.

Moreover Your post#7 does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

You're welcome.