Macro: Insert, copy and past data from sheet
 

I am using Excel 2003. I am wanting to write a macro where I will be
continuously adding a new worksheet, copying data from the worksheet to the
right of the inserted worksheet ( which will have a different name each time
), and paste into the new worksheet. I have everthing working except I get to
the point where my current macro keeps looking at the named worksheet to the
right and keeps copying and pasting from that worksheet which each time the
macro is run moves one sheet farther to the right and I want the data from
the sheet just to the right of the new one. That's where I am stuck. How do I
get it to pull from the sheet directly to the right where it will have a
different name each time the macro is run?
Please help!
--
Beginner Excel Guy.