On Jan 18, 4:06*pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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
> > *********************************- Hide quoted text -
>
> - 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
******
|
Similar Threads
