Maybe you don't need a macro?

It sounds like =index(match()) would work for you.

Take a look at Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html

Depending on what you have in those column headers (strings or dates) and if
you're entering strings or dates in the input sheet, you may need to use:

...match(text(a1,"mmm-yy"),sheet2!b1:m1,0),...

in part of the formula


wrote:

I have Months in format (Jan-07, Feb-07, to Dec-07) as headings in row
(B1 to M1) one in Sheet1 and 10 Categories in Column A starting A2 to
A11. This is a big sheet that continuously stores monthly data for
each categories (10 Categories) and I don't want this sheet to be
touched by the data entry person.
Therefore, I have a data entry Sheet2 which has the 10 Categories (A2
to A11) and only the current month is manually entered in B1 cell with
corresponding data for each category.
How can I write a macro that will copy the data entered for that
current month from the Sheet2 and paste it into the respective Month
and Category in Sheet1?

--

Dave Peterson