Copy everything in a worksheet vba

Question

' Copy
wb.Sheets(wsSource.Name).Range("A1:W79").Copy
' Paste Special
wbTarget.Sheets("Sheet1").Range("A1:W79").PasteSpecial xlValues
wbTarget.Sheets("Sheet1").Range("A1:W79").PasteSpecial xlFormats

This code allows me to copy everything in a range of A1:W79. How can I modify this Range so that it selects everything that contains value in a worksheet. For example, worksheet might contain values from C7:G20 etc.

Answer 1

I'd go like follows:

With wb.Sheets(wsSource.Name).UsedRange
    .Copy
    With wbTarget.Sheets("Sheet1").Range(.Address)
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With
End With