I'm new here as well as new to vba. Im trying to copy the row based on selected columns.

what Im trying to ask is,

I just want to select the details of 2 & 3 like

I tried this code, but it's just copying the S.No ,not with that respected details

Sheets("I yr").Range(cellFrom1 & ":" & cellTo1).Copy Destination:=Sheets("final").Range("B8:B37")

here in this code:

cellFrom1 = Sheets("I yr").Range("N4").Value
cellTo1 = Sheets("I yr").Range("O4").Value

Here in this img [The N & O row] [1]:

In order to copy to clipboard the values in the 2nd Row, you would have to use the following Range

Range("A3:C3").Copy 'selecting 3 Cells => 3 Columns in 1 Row'

If you want to expand that to 2 Rows you should use

Range("A3:C4").Copy 'selecting 6 Cells => 3 Columns in 2 Rows

You can subsequently paste those values to the different sheet/range.

I am not sure if your intention is to paste it to the Range("B8:B37") (as that is not identified in your question). Though pasting it to a single column Range will set the values in vertical format repeating the sequence as many times as to fill the destination range.

Not sure if that is the intention, but that is the result...

so this:

Sheets("I yr").Range("A3:C4").Copy Destination:=Sheets("final").Range("B8:B37")

will result in this:

Now, if you could elaborate on what the data in the image has to do with the data in the table you have provided maybe we can make it into a coherent question and subsequent answer. Everywhere where you see an 'assume' in my answer requires more correct detailing from you.

CodePudding user response：

ok, going out on a limb here, based on a lot of assumptions...

If I assume your XL sheet/data looks something like the below with the formulas as displayed:

And your intention is to copy the data in 3 Columns, from Row 2 through to Row 22 in Sheet("I yr") to Sheet("final") writing the data at Sheet("final").Range("B8") and below.

You could do the following:

Sub T()
   
    Dim wb As Workbook
    Dim wsRead As Worksheet, wsWrite As Worksheet
    Dim rngRead As Range, rngWrite As Range
    Dim cellFrom As String, cellTo As String
    Dim topLeft As Range, btmRight As Range
   
    Set wb = ThisWorkbook
    Set wsRead = wb.Sheets("I yr")
    Set wsWrite = wb.Sheets("final")
   
    cellFrom = wsRead.Range("N4").Value2
    cellTo = wsRead.Range("O4").Value2
    
    Set topLeft = wsRead.Range(cellFrom)
    Set btmRight = wsRead.Range(cellTo).Offset(0, 2) 'offset the range address by 2 columns to the right
       
    Set rngRead = wsRead.Range(topLeft, btmRight)
    Set rngWrite = wsWrite.Range("B8")
    
    rngRead.Copy
    rngWrite.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
End Sub