How to copy only the headers and columns of requested

I have a spreadsheet that is forever growing and there are around 50 or so columns. Some of the reports that I have to make only need to contain certain columns. The problem is i cant do a range because we are always adding columns but the columns are defined by a header row (row 1).

Is is possible to write a vba script that will only copy the columns I need and copy them to a new worksheet via their column name and delete all other columns?

For example...if i wanted to keep columns "State, City, Region, Market" and delete all other columns and then paste the data to a new worksheet what would be the best approach.

Thanks for any help or suggestions.

You may not need to copy anything, just temporarily hide columns you don't want on the report:[vba]Sub blah()
ColumnsToRetain = Array("State", "City", "Region", "Market")
With ActiveSheet
Cells.EntireColumn.Hidden = True
For Each myheader In ColumnsToRetain
Set xx = .Rows(1).Find(myheader) 'add your own other parameters for .find
If Not xx Is Nothing Then xx.EntireColumn.Hidden = False
Next myheader
ActiveWindow.ScrollColumn = 1 'not needed but less frightening
'do your report printing here
Cells.EntireColumn.Hidden = False 'restore all columns
End With
End Sub
[/vba]If this isn't suitable, you'll know a way of identifying columns by their headers.

Thanks. I like the logic. However, since the file is so large i would rather strip the data before passing it along to the end users. In addition some of the columns have sensative information that everyone should not have have access to.

I know how to write the sript to delete a column but that as well will be an manual process everytime we add additional columns to the master data set i would have to edit the macro to include the additional columns.

Would you by any chance have any additional suggestions?

Quote:

---

Originally Posted by dlburkins

Thanks. I like the logic. However, since the file is so large i would rather strip the data before passing it along to the end users. In addition some of the columns have sensative information that everyone should not have have access to.

I know how to write the sript to delete a column but that as well will be an manual process everytime we add additional columns to the master data set i would have to edit the macro to include the additional columns.

Would you by any chance have any additional suggestions?

---

Well, copy across only those columns you want, using the same technique. Now adding columns wherever to the source sheet won't matter and you'll only have to change the macro if you want to change which columns are copied over:
[vba]Sub blah()
ColumnsToCopy = Array("State", "City", "Region", "Market") 'put them in the order you want them to appear on the new sheet
Set SourceSht = ActiveSheet 'or be explicit about this (fully qualify which sheet)
Set newWbk = Workbooks.Add
Set NewSht = newWbk.Sheets(1)
NewSht.Name = "YourReportNameHere"
With SourceSht
i = 1
For Each myheader In ColumnsToCopy
Set xx = .Rows(1).Find(myheader) 'add your own other parameters for .find
If Not xx Is Nothing Then
'xx.EntireColumn.Copy NewSht.Columns(i) 'either use this line by itself if there are no formulae, or..
'use the next three lines to copy most of the formatting and just values to the new sheet:
xx.EntireColumn.Copy
NewSht.Columns(i).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
NewSht.Columns(i).PasteSpecial Paste:=xlPasteFormats

i = i + 1
End If
Next myheader
End With
Application.CutCopyMode = False
End Sub
[/vba]