Copy Range in a table based on the Header name

[AB_wannabe]

I've been searching for a code to copy a range within a table based on the name of the header for the column in the table.

The table of data is found in Sheet1 range (C5:R16). The table has headers in row 5 (headers of Months of the year) and the data below the headers.

So for example, I need a macro to copy only the data for November (instead of copying the whole table) and then paste it into a different existing workbook. Below is an example of the table.

[TABLE="width: 292"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl72, width: 64, bgcolor: #daeef3"]C5[/TD]
[TD="class: xl68, width: 64, bgcolor: #daeef3"]July[/TD]
[TD="class: xl69, width: 64, bgcolor: #daeef3"]August[/TD]
[TD="class: xl72, width: 69, bgcolor: #daeef3"]September[/TD]
[TD="class: xl68, width: 64, bgcolor: #daeef3"]October[/TD]
[TD="class: xl69, width: 64, bgcolor: #daeef3"]November[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C6 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 434,181 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 432,875 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 453,043 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 468,311 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 460,025 [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C7[/TD]
[TD="class: xl70, bgcolor: #daeef3"] 17 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 1 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 7 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 35 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 2 [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C8[/TD]
[TD="class: xl70, bgcolor: #daeef3"] 142 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 22 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 1 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 33 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] - [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C9[/TD]
[TD="class: xl70, bgcolor: #daeef3"] 32,819 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 31,115 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 29,647 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 29,996 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 27,242 [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C10[/TD]
[TD="class: xl70, bgcolor: #daeef3"] 452 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 273 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 372 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 453 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 321 [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C11[/TD]
[TD="class: xl70, bgcolor: #daeef3"] 64 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 20 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 28 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 24 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 9 [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C12 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 450 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 422 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 874 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 567 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 112 [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C13 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 4,183 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 2,803 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 2,700 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 3,338 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 3,517 [/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #daeef3"]C14 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 472,308 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 467,531 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 486,672 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 502,757 [/TD]
[TD="class: xl70, bgcolor: #daeef3"] 491,228 [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #daeef3"]C15[/TD]
[TD="class: xl71, bgcolor: #daeef3"]1.09%[/TD]
[TD="class: xl71, bgcolor: #daeef3"]0.75%[/TD]
[TD="class: xl71, bgcolor: #daeef3"]0.82%[/TD]
[TD="class: xl71, bgcolor: #daeef3"]0.87%[/TD]
[TD="class: xl71, bgcolor: #daeef3"]0.81%[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]C16[/TD]
[TD="class: xl67, bgcolor: yellow"]98.91%[/TD]
[TD="class: xl67, bgcolor: yellow"]99.25%[/TD]
[TD="class: xl67, bgcolor: yellow"]99.18%[/TD]
[TD="class: xl67, bgcolor: yellow"]99.13%[/TD]
[TD="class: xl67, bgcolor: yellow"]99.19%[/TD]
[/TR]
</TBODY>[/TABLE]

 

[JLGWhiz]

Here is an example of one method.

Dim col As Long, mo As String
mo = InputBox("Enter Full Month Name to Find") 'If you have a variable or cell location with the name then use that.
col = Sheet1.Range("C5:N5").Find(mo, , xlValues, xlWhole).Column
    If Not col Is Nothing Then
        Columns(col).Copy
            'Do stuff with data
    End If

 

[GW WXLS]

Try this:

Sub GW_WXLS()
Dim Mnth As String
Dim Wb1, Wb2 As Workbook
Dim Colnum As Long
Dim Ccopy As Range
Mnth = InputBox("Enter Month")
Set Wb1 = ActiveWorkbook
Workbooks.Add            'you didn't specify a name of a workbook so I assumed you would want it in a new book
Set Wb2 = ActiveWorkbook
Wb1.Activate
Sheets(1).Activate
Rows("5:5").Find(Mnth).Activate
Colnum = ActiveCell.column
Set Ccopy = Range(Cells(5, Colnum), Cells(16, Colnum))
Ccopy.Copy Destination:=Wb2.Sheets(1).Range("A1")
'ActiveCell.EntireColumn.Copy Destination:=Wb2.Sheets(1).Range("A1")' this could replace the 3 lines above if you wanted to copy the entire column
End Sub