# Copy a range from one Excel sheet to another as values without formatting



## sofas (Jan 1, 2023)

Hello, I want to copy data sheet1 weeping from the cell ("a5:av"& lr) and copy it in sheet2 ("a9:av") Copying is always under the last row with data in it sheet2


----------



## mumps (Jan 1, 2023)

Try:

```
Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    desWS.Range("A9").Resize(LastRow - 4, 48).Value = srcWS.Range("A5").Resize(LastRow - 4, 48).Value
    Application.ScreenUpdating = True
End Sub
```


----------



## davesexcel (Jan 1, 2023)

sofas said:


> Hello, I want to copy data sheet1 weeping from the cell ("a5:av"& lr) and copy it in sheet2 ("a9:av") Copying is always under the last row with data in it sheet2


Here are two options, using paste values & resize


```
Sub CopyToOption1()
    'using paste values
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim LstRw As Long, Rng As Range

    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    With sh1
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Rng = .Range("A5:AV" & LstRw)
    End With
    With sh2
        Rng.Copy
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With
End Sub

Sub CopyToOption2()
    'resize
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim LstRw As Long, aRng As Variant
    Dim r1 As Long, c1 As Long
    Dim Lrw As Long

    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    With sh1
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        aRng = .Range("A5:AV" & LstRw).Value
        r1 = UBound(aRng, 1): c1 = UBound(aRng, 2)
    End With
    With sh2
        Lrw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Range("A" & Lrw).Resize(r1, c1).Value = aRng

    End With
End Sub
```


----------



## sofas (Jan 1, 2023)

mumps said:


> Try:
> 
> ```
> Sub CopyData()
> ...


Thank you, but I want the data to be copied every time under each other when copying under the last cell of a column A And that the columns are copied, provided that there is a value in a column  A as well


----------



## mumps (Jan 1, 2023)

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (*not pictures*) of your two sheets.  Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).


----------



## sofas (Jan 1, 2023)

mumps said:


> It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (*not pictures*) of your two sheets.  Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).











						COPY.xlsm
					

Shared with Dropbox




					www.dropbox.com


----------



## davesexcel (Jan 1, 2023)

Did you check my answer?


----------



## sofas (Jan 1, 2023)

davesexcel said:


> Did you check my answer?


Thank you all. This is what I was looking for. Maybe you can find the same thing in another way


```
Sub transfert()

    Dim LastRow As Integer
    Dim NextRow As Integer
    Dim RowCount As Integer
    NextRow = sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    RowCount = LastRow - 4
   
    sheet2.Cells(NextRow, 1).Resize(RowCount, 31).Value = Cells(5, 1).Resize(RowCount, 31).Value
End Sub
```


----------

