VBA Save active sheet as new Workbook in specific Location Values only

[AdyStewart]

Hello

i need a macro that will Save the current active sheet as a workbook

Any help would be greatly appreciated

Requirements:
Save active sheet as a workbook Values only
Save to specific location E.G. "C:\Users\Username\Desktop"
Save the file name as "text" & " " Active sheet name

 

[MoshiM]

Hello

i need a macro that will Save the current active sheet as a workbook

Any help would be greatly appreciated

Requirements:
Save active sheet as a workbook Values only
Save to specific location E.G. "C:\Users\Username\Desktop"
Save the file name as "text" & " " Active sheet name

Sub Save_Sheet()

Dim SD As Variant, WB As Workbook, ASH As Worksheet

Set ASH = ActiveSheet

SD = ASH.UsedRange.Value

Set WB = Workbooks.Add

With WB

    .Worksheets(1).Range("A1").Resize(UBound(SD, 1), UBound(SD, 2)).Value2 = SD
    .SaveAs _
        Filename:=Environ("USERPROFILE") & "\Desktop\text " & ASH.Name & ".xlsb", _
        FileFormat:=xlExcel12 'SAVES in .XLSB Format
    .Close
    
End With

End Sub

 

[AdyStewart]

Thank you

Your code is spot on