Macro to copy certain columns (from dynamic range) to another sheet

[help44]

Hi,

I'm new to VBA / macros but am pretty sure this is the best way to solve the following challenge.

Sheet 1 contains dynamic data (in columns A to R) and is refreshed daily from an external data source. Columns A to F contain standard information that I want to copy over to Sheet 2 every time. Columns G to R contain sales amounts ($) by month - the headers for Columns G - R are months (e.g., January, February, etc) and are entered as plain text (not dates).

In addition to Columns A-F, I need to copy over one column from columns G to R depending on what the current month is (e.g., header name has to match the current month). So, for this month, I would want a macro to copy over the following data from Sheet 1 to Sheet 2:

Columns A to F (standard information)
Column L (contains June's data...column L's header is "June")

Sheet 2 will serve as the backbone for reports that show monthly sales by business area.

Any thoughts on a macro to tackle this would be greatly appreciated. Thanks in advance for your help!

 

[venkat1926]

try this macro

Sub test()
Dim r As Range, mo As Range, r1, mon As String
Worksheets("Sheet2").Cells.Clear
mon = Format(Date, "mmmm")
'MsgBox mon
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Range("F1").End(xlDown))
Set mo = Cells.Find(what:=mon, lookat:=xlWhole)
Set mo = Range(mo, mo.End(xlDown))
r.Copy Worksheets("sheet2").Range("A1")
mo.Copy Worksheets("sheet2").Range("a1").End(xlToRight).Offset(0, 1)
Application.CutCopyMode = False
End Sub