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.

C5	July	August	September	October	November
C6	434,181	432,875	453,043	468,311	460,025
C7	17	1	7	35	2
C8	142	22	1	33	-
C9	32,819	31,115	29,647	29,996	27,242
C10	452	273	372	453	321
C11	64	20	28	24	9
C12	450	422	874	567	112
C13	4,183	2,803	2,700	3,338	3,517
C14	472,308	467,531	486,672	502,757	491,228
C15	1.09%	0.75%	0.82%	0.87%	0.81%
C16	98.91%	99.25%	99.18%	99.13%	99.19%

<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>
</TBODY>

 

[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