I hope the below link will help.

http://www.rondebruin.nl/copy2.htm

If this post helps click Yes
---------------
Jacob Skaria


"wcollatz" wrote:

Hi,

I'm having trouble creating a macro to combine data from several sheets in a
large workbook into one summary sheet. The macro first creates the new
sheet, names it then I attempt to add text data to some cells before I copy
data from the other sheets. I can only get as far as creating the new sheet
because, when I ask Excel to add text to a subsequent cell (for my section
headings) on the new sheet, it overwrites the info I put in the first cell
(A1.)

I reduced the macro to just a couple if commands for troubleshooting but I
still cannot get it to work. I originally used Range("A3").Select
instead of "offset" here but both result in cell A1 (which should read
"Seasonal Summary" getting overwritten with "SOIL DATA". Any thoughts?

Code:

Sub CombineWeeklies2()

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet
Sheets(1).Name = "Summary"


' add sheet title and headings
Range("A1").Select
Selection.Font.Underline = xlUnderlineStyleSingle
ActiveCell.FormulaR1C1 = "Seasonal Summary"
Range("A1").Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "SOIL DATA"


End Sub

Thanks for your thoughts.
Wesley