paste named range data
 

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

paste named range data
 

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

paste named range data
 

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

paste named range data
 

My mistake.... the names is statRows, with an "s" on the end

However, when the range is pasted only the selected cell is filled the
rest of the cells from the named range dont get pasted.




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

paste named range data
 

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

paste named range data
 

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

paste named range data
 

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