Copy & Paste Dynamic Named Range

[EE]

Hi all

I have a database of about 10 rows and 5000 columns (Starting from
A5).

I created a cell (D2) in the file where I enter a number (say 100) and
gave it a name (NumOfRows).

I have created Dynamic Named RAnges (for CHARTING) in such a way that
from a reference cell (Say G5), it will look for the number in
"NumOfRows" and select as many rows in that column for the graph.

Now I have a need to PASTE the same data in another sheet. I assume
that for this I need to select an exact sized range to paste the named
range (correct me if I am wrong)

I am using the following code but it does not work. Where am I going
wrong?

Thanks in advance for your help.

Best
Prasad
**********************
Sub Test()
Worksheets("Data").Select
Range("J4").Select
ActiveCell.FormulaR1C1 = "X Range"
Range("J4").Select
'NumOfRows is my NAmed RAnges for the cell called
ActiveCell.Offset(1, 0).Resize(Range("NumOfRows").Value, 0).Select
Selection.Value = Worksheets("SampleData1").Range("XRange").Value
End Sub
*********************************

 

[EE]

Hi all

I have a database of about 10 rows and 5000 columns (Starting from
A5).

I created a cell (D2) in the file where I enter a number (say 100) and
gave it a name (NumOfRows).

I have created Dynamic Named RAnges (for CHARTING) in such a way that
from a reference cell (Say G5), it will look for the number in
"NumOfRows" and select as many rows in that column for the graph.

Now I have a need to PASTE the same data in another sheet. I assume
that for this I need to select an exact sized range to paste the named
range (correct me if I am wrong)

I am using the following code but it does not work. Where am I going
wrong?

Thanks in advance for your help.

Best
Prasad
**********************
Sub Test()
Worksheets("Data").Select
Range("J4").Select
ActiveCell.FormulaR1C1 = "X Range"
Range("J4").Select
'NumOfRows is my NAmed RAnges for the cell called
ActiveCell.Offset(1, 0).Resize(Range("NumOfRows").Value, 0).Select
Selection.Value = Worksheets("SampleData1").Range("XRange").Value
End Sub
*********************************

Hi folks

Have addressed the problem.

The revised code is this:

***************
Sub Test()
Dim Rng As Variant
Rng = Worksheets("SampleDAta1").Range("R2")
Worksheets("Data").Select
Range("J4").Select
ActiveCell.FormulaR1C1 = "X Range"
Range("J4").Offset(Rng, 0).Select
Range("J5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Value = Worksheets("SampleData1").Range("XRange").Value
End Sub
************************************

Any advice to simplify this is appreciated.

Best
Prasad

 

[JLGWhiz]

I didn't try this, but see if it does what you want.

 

[JLGWhiz]

It helps to include the code.

Sub Test()
Dim Rng As Variant
Rng = Worksheets("SampleDAta1").Range("R2")
Worksheets("Data").Range("J4") = "X Range"
Range("J5:J" & End(xlDown).Row).Value = _
Worksheets("SampleData1").Range("XRange").Value
End Sub

 

[JLGWhiz]

Now I have a need to PASTE the same data in another sheet. I assume
that for this I need to select an exact sized range to paste the named
range (correct me if I am wrong)

If you copy the entire row then you must paste into a range beginning in
column A and far enough from the bottom of the sheet for all the rows to fit.
However, if your range copied is less than an entire row and less than an
entire column, then you just have to make sure that there is enough space
available, it does not have to be exact, just big enough.

If you wanted to copy rows 2 - 6 then from sheet1 to sheet2 then

Worksheets("Sheet1").Rows(2:6).EntireRow.Copy _
Worksheets("Sheet2").Range("A2")

This would all be one line in VBA and would copy the five rows from sheet1
to the same location on sheet 2. It has to be anchored in Column A.

Worksheets(1).Range("A2:F6").Copy Worksheets(2).Range("C7")

This line would copy a 6 x 6 range of cells from sheet1 and paste it into
sheet2 with the top left corner anchored in Cell C7, or to Range("C7:H13")

When you copy and paste, you need to know where your data is going because
the paste action will overwrite existing data.

 

[EE]

Now I have a need to PASTE the same data in another sheet. I assume
that for this I need to select an exact sized range to paste the named
range (correct me if I am wrong)

If you copy the entire row then you must paste into a range beginning in
column A and far enough from the bottom of the sheet for all the rows to fit.
 However, if your range copied is less than an entire row and less than an
entire column, then you just have to make sure that there is enough space
available, it does not have to be exact, just big enough.

If you wanted to copy rows 2 - 6 then from sheet1 to sheet2 then

Worksheets("Sheet1").Rows(2:6).EntireRow.Copy _
Worksheets("Sheet2").Range("A2")

This would all be one line in VBA and would copy the five rows from sheet1
to the same location on sheet 2.  It has to be anchored in Column A.

Worksheets(1).Range("A2:F6").Copy Worksheets(2).Range("C7")

This line would copy a 6 x 6 range of cells from sheet1 and paste it into
sheet2 with the top left corner anchored in Cell C7, or to Range("C7:H13")

When you copy and paste, you need to know where your data is going because
the paste action will overwrite existing data.












- Show quoted text -

Many thanks for your response. Your earlier post definitely simplified
what I cam up with . By about 50% (you converted 10 lines of code I
wrote to achieve the same objective to 5.)

Best
Prasad

 

[EE]

Now I have a need to PASTE the same data in another sheet. I assume
that for this I need to select an exact sized range to paste the named
range (correct me if I am wrong)

If you copy the entire row then you must paste into a range beginning in
column A and far enough from the bottom of the sheet for all the rows to fit.
 However, if your range copied is less than an entire row and less than an
entire column, then you just have to make sure that there is enough space
available, it does not have to be exact, just big enough.

If you wanted to copy rows 2 - 6 then from sheet1 to sheet2 then

Worksheets("Sheet1").Rows(2:6).EntireRow.Copy _
Worksheets("Sheet2").Range("A2")

This would all be one line in VBA and would copy the five rows from sheet1
to the same location on sheet 2.  It has to be anchored in Column A.

Worksheets(1).Range("A2:F6").Copy Worksheets(2).Range("C7")

This line would copy a 6 x 6 range of cells from sheet1 and paste it into
sheet2 with the top left corner anchored in Cell C7, or to Range("C7:H13")

When you copy and paste, you need to know where your data is going because
the paste action will overwrite existing data.












- Show quoted text -

My code looked like this. I will change it as per your post now.

****
Sub ScenarioABCDE()
Dim Rng As Variant
Rng = Worksheets("SampleData1").Range("N2")
' The named range was in a different sheet and I did not call that out
earlier.

'Copying XRange

Worksheets("Data").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = "X Range"
Range("B4").Offset(Rng, 0).Select
Selection.Value = Worksheets("SampleData1").Range("XRange").Value
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Value = Worksheets("SampleData1").Range("XRange").Value

End Sub
******