Copy only required cells to new workbook

[CaptainGravyBum]

Hello,
I have a macro set up to copy data from one sheet onto a new workbook before saving, but the command isn't copying just the data, it appears to be pulling in 200+ blank rows as well.
Is there a way to just copy the selected cells from sheet1?

Dim savePath As Variant
Dim newWb As Workbook
Dim newSheet As Worksheet
Dim sheetName As String
Dim currentDateAndTime As String
Dim defaultPath As String

Set ws = ThisWorkbook.Sheets("Sheet1")
currentDateAndTime = Format(Now(), "ddmmyyyy_hhmmss")
sheetName = ws.Name
Set newWb = Workbooks.Add
Set newSheet = newWb.Sheets(1)
ws.Cells.Copy newSheet.Cells

 

[jkpieterse]

This "just" copies the used range:

ws.UsedRange.Copy newSheet.Range("A1")

Note that the usedrange *may* start in a different cell than A1.

 

[CaptainGravyBum]

Hi @jkpieterse
Thanks for your response. I tried this and the result is still the same unfortunately, I must be missing something with the spreadsheet because when saving as csv there are 200 rows extra created.
Maybe I'll have to admit defeat. I'm off to bang my head against a wall.

 

[MARK858]

Try running the code below on a copy of your sheet then try the code by @jkpieterse

Sub LoseThatWeightx()

    Dim xx As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    With ActiveSheet
        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
        .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
    End With
   
    xx = Application.ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = True
End Sub

 

[CaptainGravyBum]

Thanks @MARK858, that has worked perfectly alongside the information from @jkpieterse !!
You are life savers!

 

[MARK858]

You're welcome, happy we could help