Copy whole rows from another workbook if condition is met then filter those rows

[VBA_Casualnewbie]

Hello dear Excel Forum community,

this is my first time describing an Excel-Problem, so please don't be too harsh -I will try to give you every Information you Need to be able to help me. :D

[disclaimer: The workbooks are attached to this post.]

The situation I'm stuck (with my primitive "copy code and hope it works" method) at is the following:

I work with 2 workbooks. One contains the data of People (e.g. adress, phone number, etc.). At the last colum (e.g. "J") is my condition, if I want to copy this specific (but the whole!) row: "yes" or "no".

The data is copied to another workbook, which contains a Queue in which the People with the condition "yes" get sorted into.


My current solution is kinda crappy - I know that. It works, but only if I (the one who wrote it) maintains it every few weeks and so on. I want it to work even with someone who doesn't know what it does.


Here goes:

At the Moment, I have in my workbook #1 my list of data with the condition yes or no (please see pic#1)

I copy this list to my other workbook#2 with simple ref-links into sheet #2 (please see pic #2) (please note that in my Picture the data is "invalid" - because the valid data is already cut to sheet#1)

In my workbook#2 there is a VBA-Code, which helps me filter the "yes" conditions from sheet#2 to my sheet #1(the Queue) (please see pic#3).

My Problem: At the Moment, I Need to click into the "yes" condition in my workbook#2 in sheet #2, otherwise the VBA-Code won't cut the data to my sheet#1 (I have to activate the vba by having a makro at Startup click manually into the cells with the condition - please see pic#4 for this "click into cell" code).

This - I could live with (even if it's a shitty solution). BUT: The cells which I Need to have checked every Startup will get more and more - the more People come into my data-file. At the Moment I only check cell 1-100, which takes about 10 seconds at start-up. But I would have to update the range in which to check every few weeks, so I won't have to wait 5 minutes for the file to check Range K1-1000.

The Problem: I get the data from workbook#1 to workbook#2 by ref-links - which are cut by my vba. And I am Kind of lost - I can't imagine a solution, which fixes my Problem "in the Long run".

I would be grateful for any ideas. I do hope someone actually has the Patience to read through this mess of mine.

Thanks and best wishes, VBA_Casualnewbie.

[jindon]

If upload those WORKBOOKS with before/after, it will help.
Pictures doesn't help at all...

[VBA_Casualnewbie]

If upload those WORKBOOKS with before/after, it will help.
Pictures doesn't help at all...

oh - of course. Sorry, didn't think of this (don't know why actually). I will upload the workbooks in a few minutes, Need to clear them of critical data beforehand.

thanks!

[VBA_Casualnewbie]

Sorry - I can't upload the workbooks from here (Firewall). I'll upload them asap. Thanks for your Patience!

I've uploaded them in the main post. Thanks!

[VBA_Casualnewbie]

The Problem is still on my priority-list. :D

[jindon]

Do you just want to filter "yes" and output to "QUEUE" sheet and "no" to "SHEET2" from "ALTE_VG" sheet in #1 book?

[VBA_Casualnewbie]

Do you just want to filter "yes" and output to "QUEUE" sheet and "no" to "SHEET2" from "ALTE_VG" sheet in #1 book?

If possible, the only Thing I really want is to filter the "yes" condition from book#1 to "QUEUE" sheet from book#2, but only with VBA and not with e.g. "=ALTE_VG[$A$2]" ref links.

I don't know how though - so at the Moment I work with the solution shown in my two workbooks - I copy every entry from book#1 "ALTE_VG" to sheet2 in book#2 and then filter to "QUEUE" - but I would prefer a possible shortcut to that solution.

the downside of my current solution is described above - the Need to "check" the condition cell every Startup and the Problem, that sheet2 will go down forever till it reaches row9999 e.g. and is not "updated" to the rows 1-50.

[jindon]

If possible, the only Thing I really want is to filter the "yes" condition from book#1 to "QUEUE" sheet from book#2, but only with VBA

Try

Please Login or Register  to view this content.

[VBA_Casualnewbie]

Hey jindon,

thanks for your Patience and help!

I've run into a few questions after trying:

where do I put the code? at the Moment I've put it into "this worksheet" in workbook#1.

second: do I Need to replace the "Excelfiles,*.xls*" with the Name of the workbooks? if my workbooks are named e.g. "workbook1" and "workbook2", would the code be

fn = Application.GetOpenFilename("workbook1.xlsm,workbook2.xlsm")

or would it be

fn = Application.GetOpenFilename("workbook1;workbook2,*.xlsm*")

or something like that?

[jindon]

When dialogue box appear, select workbook that have data(dataworkbook#1).