Conditional Copy Macro

[Naith]

Hi,

Is it possible to conditionally copy certain columns from an Excel worksheet to another sheet in the same workbook, removing any column gaps?

e.g.

Source: Worksheet A

Col1      Col2  Col3
--------------------
Critical  1234  S.Smith
Critical  1234  J.Black
High----  2222  B.Taggett
High----  2222  B.Taggett
Medium--  3443  K.Feeder

Target: Worksheet B

Col1      Col3  
--------------
Critical  S.Smith
Critical  J.Black

Note, only the 'Critical' value in Col1 drives the copy.

I'm sure this can be achieved with a macro, but in this area, I'm something of a newbie.

Thanking you kind folk in advance,
Naith

 

[combo]

Use filter (autofilter): copy filtered columns (select only those you need) and paste into new sheet. If you have formulas, only values will be pasted.
Depending on required report shape, a pivot table could be useful too.

combo

 

[SkipVought]

Hi,

The way I would is via a query. Would take about 30 seconds.

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Naith]

That was quick. You guys are on the ball around here.

Combo, thanks for your contribution, but that is a manual process. This needs to be automated, which is why I alluded to a macro.

Skip, thanks for pointing me to the FAQ. My concern here is that I would have to explicitly name the data source. A detail that I omitted in the original post, but which becomes valid if looking at this solution is that the excel files which would need to undergo this editing of data are created on a daily basis with discreet naming functionality.

e.g. theexcelfile010809

Therefore, I cannot see that using a query would be an entirely automated process, as it would need maintaining to accommodate the changing data source names. Unless I am misunderstanding the impact of queries?

Is there a benefit to using a query for this instance over using a macro? If a macro is the way to go, if someone could post an example of code, I'd appreciate it.

Thanks

 

[SkipVought]

The macro would be to find the correct data source, like theexcelfile010809.

What is the logic for finding this file?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Naith]

Essentially, it'll be the most recent chronological file in the source directory.

However, seeing as the intended copy process is from local sheet to local sheet within the same workbook, if there's a solution which doesn't reference a file name, that would work.