Selecting UsedRange in Columns and Rows in Sheet

Question

• I have two sheets in my excel file, DTMGIS and DTMEdit
• the DTMEdit sheet is empty
• and I am trying to xopy everything from DTMGIS and Paste only Values to Sheet DTMEdit

I do not know how to select the UsedRange only and paste this to DMTEdfit

Dim ws As Worksheet
Dim LastRow As Long, LastCoulmn As Long, Header As Long
Header = 2
Set ws = ThisWorkbook.Sheets("DTMGIS")
LastRow = ws.UsedRange.Rows.Count
LastCoulmn = ws.UsedRange.Column.Count

With ws.UsedRange
    .Select
    .Copy
End With

Answer 1

A variation below which

• avoids the assumption that the UsedRange starts from A1
• runs the copy without moving the formats as well (if you do use .Copy use Application.CutCopyMode = False at the end)

code

Sub FastCopy()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = ThisWorkbook.Sheets("DTMGIS")
    Set ws2 = ThisWorkbook.Sheets("DTMEdit")
    ws2.Range(ws1.UsedRange.Address).Value = ws1.UsedRange.Value
End Sub

Answer 2

Try something like this:

Sub CopyPasteValues()
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set ws1 = ThisWorkbook.Sheets("DTMGIS")
    Set ws2 = ThisWorkbook.Sheets("DTMEdit")

    ws1.Range(ws1.UsedRange.Address).Copy
    ws2.Range("a1").PasteSpecial xlPasteValues
End Sub