ExcelBanter - View Single Post

[email protected]

ahh, that works nicely
wrote:
Hi Robert,

The copy function would take over the whole range. You can use it like
so:

Worksheets("Template").Range("statrows").Copy Selection

Regards,
Steve

wrote:
I just needed to resize the selection to match the named range:

Range("a1").End(xlDown).Offset(2, 0).Select

Selection.Resize(10, 60).Select

Selection.Value = Worksheets("Template").Range("statrows").Value

Although it seems like there should be a way to make it just dump the
whole range in without requard to size.

wrote:
Steve,

I updated the code with your suggestion to:

Range("a1").End(xlDown).Offset(2, 0).Select

Selection.Value = Worksheets("Template").Range("statRow").Value

I still get an "Run Time Error 1004 Application Defined or Object
Defined Error."

Thanks for the help

wrote:
Hi Robert,

You don't need to use the names collection. Just say:

Worksheets("Template").Range("statRow").Value

Regards,
Steve

wrote:
I have a macro in a workbook that creates worksheets and copies data
from other sheets to the new sheets. I also have a template sheet
that has large range of cells with formulas that I want to copy to the
new sheet after the rest of the data is in place. I'm using a named
range for the location of the template cells "Template!statRow".
The following code is initiated in the new worksheet and activates the
correct cell to paste the cells from the names range but I cannot seem
to get the pasting in correct.

Public Sub InsStatRow()
'Insert Statistics rows

'move to insertion point
Range("a1").End(xlDown).Offset(2, 0).Select

Selection.Value = Worksheets("Template").Names("statRow")

End Sub

I get a runtime error 1004 at the line
Selection.Value = Worksheets("Template").Names("statRow")

I've tried many variations, to no avail. Any help will be
appreciated.
Robert