i'm using the Cells.Find method to locate a column by it's heading (top cell)-works well (example below), but when the column is filled with links to other cells (e.g., =sheet1!a1), the find method doesn't work correctly...?

'copy out (after filer and before sending to new file)
If City.value = True Then
Cells.Find("City", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Offset(1).SpecialCells(x
lCellTypeConstants).Select

???

thank you.

Why don't you search the row the headers in instead of all the cells in the worksheet?

oh...i like that idea (better)...

may i ask for an example (all my row hearders are in row 8)...?

thank you

Replace Cells with Rows(8).

oh...and i can do the find method the same (keep the rest of the line of code)...?

thank you...(interesting...)

As far as I can see, give it a try.

(i couldn't go to lunch before i tried...) eureka! it works...

thank you.

now, one more issue (that i sometimes had before too), on one column heading (below), i get a "type mismatch error":

Rows(8).Find("Nameof", After:=rFoundCell).EntireColumn.SpecialCells(xlCellTypeConstants).Offset(1).Spec
ialCells(xlCellTypeConstants).Select

'After:=rFoundCell) returns empty (my other columns are working...)

???

thank you...!

Why not split that long line of code up a bit?

You could start with something like this to find the header.

So if the header is found you do whatever else you are doing.

By the way, what is this meant to do?

thank you...i will try tomorrow...

the user wants to filter and then select certain columns (the code example) to export to a new workbook...
(so basically, automated column selecting vs. the user selecting a column and deleting before exporting from the master xls...)

wdyt?

thanks!

You don't need to select to do any of that.

In fact you might be able to do the filter with advanced filter.

With advanced filter you wouldn't need to know which column had the header, you could just use it in the criteria.

You can also copy to another location, eg a range or sheet, then you could copy the columns you want to the new workbook.

thank you...

as i ponder, may i ask for a line or two to look at...
(i want to filter a range and then select a column(s)-and they can be nonadjancent-of the data range and copy out to another workbook)

thank you...

I don't have any example data to hand, perhaps you could attach a file with soem?

thank you...

how about if i give you some of the code snippets (to start)
algorithm (user makes selections from popup userform):
-filter
-select specfic columns
-copy out to xls

thank you...!

n.b., other than my column finding sometimes error-i'm working on..., the basic code concept is working...(in the sandbox)

‘FILTER
Dim rssalestest, filteredRange As Range
Set rssalestest = Range("planningdatabig")
For x = 0 To DistNames.ListCount - 1
If DistNames.Selected(x) = True Then
mydistname = DistNames.List(x, 2) 'grab hidden columns in query
mydistnbr = DistNames.List(x, 1)
MYfullname = mydistnbr & mydistname
rssalestest.AutoFilter field:=1, Criteria1:=mydistnbr, Operator:=xlAnd
rssalestest.AutoFilter field:=2, Criteria1:=mydistname, Operator:=xlAnd
rssalestest.AutoFilter field:=4, Criteria1:=brandsegment, Operator:=xlAnd
rssalestest.AutoFilter field:=5, Criteria1:=BrandFamily, Operator:=xlAnd
…
‘COPY OUT COLUMNS
'copy out (after filer and before sending to new file)
If City.value = True Then
Rows(8).Find("City", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Offset(1).SpecialCells(x
lCellTypeConstants).Select
Selection.Copy
Worksheets("temp").Select
Range("a1").Select
Selection.PasteSpecial
Worksheets("planningdata").Select
End If
If NBR.value = True Then
Rows(8).Find("Distributor #", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Offset(1).SpecialCells(x
lCellTypeConstants).Select
Selection.Copy
Worksheets("temp").Select
Range("a1").Select
Cells(Selection.Row, Columns.Count).End(xlToLeft).Offset(, 1).Select
Selection.PasteSpecial
Worksheets("planningdata").Select
...(for all the columns of interest…

COPY OUT TO NEW WORKSHEET

Dim wb As Workbook
Dim ws As Worksheet
Set ws = ActiveSheet

'Set wb = Application.Workbooks.Open("c:\temp\temp.xlsm")
'Set wb = Application.Workbooks.Add("c:\temp\temp.xlsx")
'wb.SaveAs Filename:=("c:\temp\temp.xlsx")
Set newbook = Workbooks.Add
With newbook
.Title = "All Sales"
.Subject = "Sales"
'.Sheet.Name = "comm"
ActiveCell.PasteSpecial xlValues
'.SaveAs Filename:="c:\temp\temp.xlsx"
.SaveAs Filename:="c:\temp\" & MYfullname & ".xlsx"
End With

ActiveSheet.Name = "abc for " & Trim(ComboBox2.value)
Sheets("sheet2").Delete
Sheets("sheet3").Delete
Cells.Select
Cells.EntireColumn.AutoFit
Range("a1").Select
newbook.Close
…

thanks...

Without data I can't do really do anything with the code.

Couldn't you make up some dummy data on a worksheet and attach it here?

PS Are you not using the code I suggested?

thank you...

i'm back on testside and i tried:

Set rngFnd = Rows(8).Find("Nameof", After:=rFoundCell)

but i get a "type mismatch" error

???

thank you.

Are the headers from a table?

yes. column headers are in row 4.

thank you.

and it's continous range from a4:f9 (testarea).

But earlier you said the headers were in row 8?

Is it not possible to attach a workbook with example data?

That would help a lot.

well, i made a testside, just to practice...

ok...working on it...

thank you.

here's my example:

thank you!

You get the type mismatch because rfoundcell is nothing.

It should be a range.

ok...

i added range name: "Headers" as a4:f9

dim...
set rngfnd = range("headers")
set... (i get the type mismatch here...)

?

thank you.

You get the error on this exact line of code?

set....

set rngfnd - rows(4).find("State", after:=rfoundcell)
(i believe the same as in the attachement-i'm trying to duplicate (exactly))...

thank you.

ok...

Rows(8).Find("City", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Offset(1).SpecialCells(x
lCellTypeConstants).Select

is working well and i would like to copy the three rows above too.

???

thank you.