Copying data to a new sheet to run advanced filter

[sorensjp]

Hello All,

I'm attempting to take input data from one data sheet ('ACT_MEM' Sheet) to ('WEAPS_RPT_DATA' Sheet) so I can run an Advanced Filter. The idea is to have a readable table of data available for printing or viewing. The 'ACT_MEM' Sheet contains data that is entered from two separate worksheets.

I've been able to copy the correct columns of data from 'ACT_MEM' Sheet to the 'WEAPS_RPT_DATA' Sheet. But I would like to speed up the process with better coding, which is beyond my understanding. Or could I run an Advanced Filter directly from the 'ACT_MEM' Sheet to the 'WEAPS_RPT_DATA' Sheet? I have attempted to without success.

Thank you in advance for your time to look at my question and issue.

V/r,
Jim

[Norie]

Jim

What data do you want to copy from 'ACT_MBR' and how do you want to filter it?

[sorensjp]

Norie,

I want the data from 'ACT_MBR' going to 'WEAPS_RPT_DATA' Sheet in the order of columns "A:AR". Then I use an Advanced Filter to take the data to 'WEAPS_QUAL_RPTS' Sheet filtered and sorted.
-- The data is entered onto 'ACT_MBR' form two sperate DATA entry worksheets ('CREW_INFO' and 'WEAPS_QUALS') and each data Entry worksheet uses a portion of each others copied data.

I made an error in my question above:

I've been able to copy the correct columns of data from 'ACT_MEM' Sheet to the 'WEAPS_RPT_DATA' Sheet. But I would like to speed up the process with better coding, which is beyond my understanding. Or could I run an Advanced Filter directly from the 'ACT_MEM' Sheet to the 'WEAPS_RPT_DATA' Sheet? I have attempted to without success.

I should have asked:

**Or could I run an Advanced Filter directly from the 'ACT_MEM' Sheet to the 'WEAPS_QUAL_RPTS' Sheet?

V/r,

Jim

[sorensjp]

Hi,

I found a string that seems to be lighter and easier to use (I'm guessing).
https://www.mrexcel.com/forum/excel-...-vba-code.html

I tried to take the a few columns from my extraction data 'ACT_MBR' and paste to the 'WEAPS_RPT_DATA' sheet. I get a Compile error: Expected Array on the

Please Login or Register  to view this content.

portion of the code.

Please Login or Register  to view this content.

Does this method have any merit toward my goals? I am very weak in VBA coding and have been trying to understand it better.

Again, Thank you for your thoughts and time.

V/r,
Jim

[Norie]

Jim

So in the workbook you attached A3:AR16 would be copied from 'ACT_MBR' to 'WEAPS_RPT_DATA'?

How is that data then filtered/sorted?

As for the code in post #4, the problem is with the declaration of myColumns.

myColumns should be declared as Variant, not string.

Please Login or Register  to view this content.

[sorensjp]

Norie,

Yes that would be correct. The Data residing on 'ACT_MBR' is much more than required for the 'WEAPS_RPT_DATA'. So with that copied data on 'WEAPS_RPT_DATA' sheet I run an Advanced Filter to place the finished data on the 'WEAPS_QUAL_RPTS' Sheet. There its clean and available review, printing and using basic On Sheet Filtering for a user. Or at least that's the idea.

I'm thinking I should reformat the how the data gets saved on the 'ACT_MBR' Sheet so it can easily be sent to the 'WEAPS_RPT_DATA' Sheet. Since It takes a lot of time to copy and move the data over which most if it already formatted the correct way.

Anyway Thank you for taking a look at my code. I adjusted the code in post #4 and the data did not copy and paste over to the correct sheet. Maybe I have it not properly set the paste location or something.


V/r,
Jim

[Norie]

Jim

Are you actually filtering the data based on some criteria or is it that you only want certain columns to be copied?

[bakerman2]

Give this a go. Push button on 'WEAPS_RPT_DATA' Sheet.

Please Login or Register  to view this content.

[sorensjp]

Norie,

I was only going to copy the data and then run filters (as needed) for the 'WEAPS_QUAL_RPTS' when a user is accessing that Sheet. I don't think it necessary to filter the copied data yet.


V/r,
Jim

[sorensjp]

bakerman2,

Wow, that code is very quick. My initial though is that it works for what I'm looking to do in this part of the project. It's very late here and I need to rest. I will do more testing. How could I run this code say upon Log-In? I intend to add that feature as well. I guess I'm asking where best to place it. My thought is if data has changed and that sheet does not auto up-date. Thank you for your input.

This project is been very long in creating and am close to finishing. Again, Thank you.
V/r,
Jim

[bakerman2]

Maybe the Worksheet_Activate-event from 'WEAPS_RPT_DATA' Sheet.

[sorensjp]

bakerman2,

Thank you for your reply. I was going to hide that sheet along with a couple others. I intend on using the sheets holding the tables as the "backend" for a multi-user application, well that is what I hope to do.

V/r,
Jim

[bakerman2]

Then maybe on the Worksheet_Deactivate of 'ACT_MBR' sheet.

It all just depends on how you are planning to use the file (sequence of actions taken)

[sorensjp]

bakerman2,

Thank you for your reply. I was going to hide that sheet along with a couple others. I intend on using the sheets holding the tables as the "backend" for a multi-user application, well that is what I hope to do.

V/r,
Jim

[sorensjp]

bakerman2,

Thank you for the code. Ultimately, it does exactly what I needed. I will apply it in a couple different areas with-in the project. Also I decided I could just restructure where the data is saved in the on the 'ACT_MBR' Sheet.

Again, Thank you.

Norie,

Thankyou for asking me how and what I intend on doing with the data. I made me re-think how I set up the 'ACT_MBR'.

V/r,
Jim

[bakerman2]

You're welcome and thanks for rep+.

Good luck with your project and you know where to find us incase you need some more assistance.