New range overwrites other range in create sheet macro

W

wcollatz

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2
Error Message - Macro to Unlink MS Queries from Workbook 0
Copy worksheet, trying to paste to new workbook error assistance. 1
Run macro from active workbook whose name keeps changing 20
Loop Repeats - PLS HELP 2
Show "Summary" when macro has run 4
Copying same data range to a summary sheet 8
conso macro 0

Top