Macro to Export a range within a sheet as an .xls file

[howard]

I have tried to write code to export a range A1:U25 on sheet Sales 850105 as a .xls file


Howveer, when exported, it contains the the entire used range for eg A1:AB25

It would be appreciated if sommeone could kindly amend my code

 Sub Export_Sales850105()
         Application.ScreenUpdating = False
    
    Sheets(Array("Sales 850105")).Copy
    Worksheets(1).Range("A1:U25") = Worksheets(1).Range("A1:U25").Value2
    Worksheets(1).Cells.Hyperlinks.Delete
    
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "Sales 850105 Upload" & ".xls", 56
    ActiveWorkbook.Close SaveChanges:=False
    
    Application.ScreenUpdating = True

End Sub

 

[Domenic]

Try...

 Sub Export_Sales850105()
 
    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = Worksheets("Sales 850105")
    
    Dim newWorkbook As Workbook
    Set newWorkbook = Workbooks.Add(xlWBATWorksheet)
    
    With sourceWorksheet.Range("A1:U25")
        newWorkbook.Worksheets(1).Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
 
    newWorkbook.SaveAs ThisWorkbook.Path & "\" & "Sales 850105 Upload" & ".xls", 56
    newWorkbook.Close savechanges:=False

End Sub

Hope this helps!

 

[howard]

Many Thanks Domenic. Code works perfectly