Save sheet as copy with range

[martinus1988]

Hi all,
the solution is probably pretty simple but i cannot figure it out. The macro saves the workbook sheet as hard copy (whitout formulas). But he is copying the whole sheet. Iam looking for a ranged version (A1-K139)

Sub Save_To_Excel()
Dim wb As Workbook
    Sheets("Offerte").Copy
        With ActiveSheet.UsedRange
        .Value = .Value
    End With
    Set wb = ActiveWorkbook
    wb.SaveAs ThisWorkbook.Sheets("Offerte").Range("M1").Value & ".xlsx"
    wb.Close SaveChanges:=False

End Sub

Please help

[dflak]

I think what you need to do is create a new workbook and copy and paste into it. Read up on the SaveAs command. I have not tested this code.

Sub Save_To_Excel()
Dim wb As Workbook
    Set wb = Workbooks.Add
    Sheets("Offerte").Range("$A$1:$K$139").Copy
        wb.Sheets(1).pastspecial xlPasteValues
    
    wb.SaveAs ThisWorkbook.Path & "\" & ThisWorkbook.Sheets("Offerte").Range("M1").Value & ".xlsx", _
        FileFormat = xlOpenXMLWorkbook
    wb.Close SaveChanges:=True
End Sub

[AlphaFrog]

This copies includes formats as well.

Sub Save_To_Excel()
    With Workbooks.Add(xlWBATWorksheet).Sheets(1)
        .Name = "Offerte"
        ThisWorkbook.Sheets("Offerte").Range("A1:K139").Copy .Range("A1")
        .Range("A1:K139").Value = .Range("A1:K139").Value
        .SaveAs ThisWorkbook.Sheets("Offerte").Range("M1").Value & ".xlsx"
        .Close SaveChanges:=False
    End With
End Sub

[dflak]

AlphaFrog: a quick question. On SaveAs, does the file format default to xlOpenXMLWorkbook? Too bad VB doesn't detect the extension and pick the right fileformat for you.

[AlphaFrog]

AlphaFrog: a quick question. On SaveAs, does the file format default to xlOpenXMLWorkbook? Too bad VB doesn't detect the extension and pick the right fileformat for you.

Yes. I think it does default to xlOpenXMLWorkbook

Quick heads up on your suggested code; I think you missed this bit.
Thisworkbook.Sheets("Offerte").Range("$A$1:$K$139").Copy
Otherwise it defaults to the ActiveWorkbook. In your code, the ActiveWorkbook is now the previously added new workbook.

[dflak]

Got me on that one Yes, activeworkbook is the last workbook opened or created.