Selecting from a column by name

[jomili]

Okay, I always get confused on this:

My headers exist in Row 12.
An autofilter is applied.
There's a column with the header "YTD Expense\Avg FTE", which constantly moves around as columns are added and removed from the sheet (but it should always be on row 12). I'll call that my Target column.

There MAY or MAY NOT be data under the pertinent data in my target column (if so, it will be separated by at least two blank lines).

I want to copy the visible cells in my target column, under (not including) the header, to the bottom of the filtered data, above any data that may be under it.

Any takers on how to do this? I can do all the copying, I just don't know how to zero in on my desired target column and my desired data within it.

Any help is greatly appreciated.

[benishiryo]

hi jomili, i did a little recording & adding of variables to come up with this:

Please Login or Register  to view this content.

from what i understand, there are cells separated from the filtered data below you dont want to copy? for eg if the header is in G12, & data is from G15 to G20. There can be some more data in G30:G32 you dont want? my code will copy from G13 to G20 & end there

[jomili]

Benishiryo,

It looks like your code should work just fine, but the workbook I'm trying it in is made be another group. It's protected in various ways, and some macros don't behave the way they should. I'm getting an error at the Range("A1").select line. I made a cheap little macro to test just that:

Please Login or Register  to view this content.

Running that (or your code) give me a run-time 1004 error.

I can manually select A1, but don't seem to be able to do it with a macro. Any thoughts? I can break the password and unprotect the sheet, but don't know the password to reprotect it, and this is a workbook that's updated by various people, so all the protections have to remain in place.

[jomili]

Benshiryo,
I made a change; I've now unprotected the worksheet, so I can apply your macro, and I'm getting a runtime error 91 "Object Variable or With block variable not set" on the "Cells.Find" line. Below is my macro as I've got it configured so far.

Please Login or Register  to view this content.

[Marcol]

Hmm?

Maybe you don't need to use a macro for this.

Try using dynamic named ranges

Name = "Target"
Refers to:=

Please Login or Register  to view this content.

Name = "TargetColumn"
Refers to:=

Please Login or Register  to view this content.

You can now do any calcs regardless of where the "Target" is in row 12,and how long it turns out to be.

See this workbook
Sheet2 copies the column to a fixed site, where you can filter it for non-blanks if you still need to.

[jomili]

I've actually tackled the problem from another angle; instead of trying to do anything in the workbook, I'm just doing a VLookup to it. But that also presents difficulties, as I have to determine which column in the workbook my target column is at. I can achieve this by first opening the workbook, then doing a VLookup to it, then closing it. I'd like to be able to do it without the opening/closing. Below is my code thus far:

Please Login or Register  to view this content.

[jomili]

Marcol,

Sorry I didn't reply directly to your solution. The workbook/sheet is protected; when I try to add named ranges the whole Name Manager dialogue is greyed out.