| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste to next empty cell in named range of cells | Excel Discussion (Misc queries) | |||
| Copy and Paste 2 columns from a named range? | Excel Programming | |||
| paste values from named dynamic range to another worksheet | Excel Programming | |||
| Paste a named range to another range in Excel | Excel Discussion (Misc queries) | |||
| How to paste only certain columns from a listbox into a named range | Excel Programming | |||
Hybrid Mode
