# Calc: Copy and paste named ranges [closed] edit

Is there a way to copy and paste the cell contents of a named range in another worksheet, by a given cell value. for exaple

Firts of all "RANGE_1" is at sheet 2 (A1:B5) A column is text B column are numbers

In sheet 1 cell a1 contains the text "RANGE_1" I whant to have a macro triggered by a button that when pressed it gets the range name from cell a1 at sheet 1 then it goes to sheet 2 gets the contents from the existing RANGE_1

an paste all in sheet 1 starting at A3

Is this even possible?

Thank you JohnSUN, it works just like you said, thank you very much. I guess I asked the wrong question. Let see if I explain my self better this time. Range_1 is in sheet 2 - A2:B6, and the B column has some formulas that use sheet 2 - cell B1(the one directly above the second column of the range), what I want to accomplish is to have Range_1 pasted at sheet 1 cell A3 and have the the text in the A column and the B column formulas in the range interact with the cell just above the newly pasted range, so if I paste Range_1 in C45 the formulas will interact with with D44. Like doing a manual copy of A2:B6 and pasting special >> text, formulas and numbers

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-18 23:08:42.250239

I guess a file is worth 100words, in sheet one 1(MAIN) I want to click on Cell B5 to set it as current, then select a name from the combo box, then click on the push button to run a macro to copy&paste at active cell the named range in the combo box. examp >https://www.box.com/s/ayv08oftb9e17lg6yy6j

( 2012-12-06 00:54:16 +0200 )edit

I think I have to copy the range values and formulas and then paste but I can figure out how also I found this example but I just cant implemented>> http://www.oooforum.org/forum/viewtopic.phtml?t=59919

( 2012-12-06 00:55:44 +0200 )edit

Sort by » oldest newest most voted

Of course it is possible. But it requires a good understanding of formulas Calc. You did not say what formula would be used. Suppose you want to get a product of every left cell with the value of the top cell. That is for your example it would be formulas:

#    A       B
1 Values    8,2
2    2    =B1*A2=16,4
3    5    =B1*A3=41
4    8    =B1*A4=65,6
5   11    =B1*A5=90,2
6   14    =B1*A6=114,8


Now just select range B2:B6 and press Ctrl+F3.

Attention! Active cell (with black border) must be B2!

Add new named range with name, for example, "FormulaForRange". At field Range write formula

A2:A6*B1


Now fill range E24:E28 (or J6:J10, or AKG533:AKG537...) with some values, set coefficient at F23(or K5, or AKH532...). In cell F24 (or K6, or AKH533...) type =FormulaForRange and press Ctrl+Shift+Enter

It is not difficult. But copy a range of cells, and Ctrl+Shift+V->Formulas easier

more

Yes, of course it possible

When describing RANGE_1 use absolute addresses (with dollar signs) - $Sheet2.$A$1:$B\$5

Select cell A3 on Sheet1, type

=RANGE_1


and press Ctrl+Shift+Enter

The formula in cell A3:B7 is {=RANGE_1} and show values from named range RANGE_1

more

Excellent explanation! Why is crtl+shift+enter needed?

( 2012-11-06 08:18:41 +0200 )edit

Because this is an array formula

( 2012-11-06 10:49:49 +0200 )edit

Thanks, I learned something. Haven't used yet any array formula.

( 2012-11-06 11:23:38 +0200 )edit
{"extraction_info": {"found_math": true, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 1, "mathjax_display_tex": 0, "mathjax_asciimath": 1, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "math_score": 0.28227493166923523, "perplexity": 4407.563026642667}, "config": {"markdown_headings": true, "markdown_code": true, "boilerplate_config": {"ratio_threshold": 0.18, "absolute_threshold": 20, "end_threshold": 15, "enable": true}, "remove_buttons": true, "remove_image_figures": true, "remove_link_clusters": true, "table_config": {"min_rows": 2, "min_cols": 3, "format": "plain"}, "remove_chinese": true, "remove_edit_buttons": true, "extract_latex": true}, "warc_path": "s3://commoncrawl/crawl-data/CC-MAIN-2020-45/segments/1603107874026.22/warc/CC-MAIN-20201020162922-20201020192922-00116.warc.gz"}