VBA Code - Save Worksheet as New Workbook with Certain Name

[shaon814]

Hi,

I'm trying to find a Macro to save the current worksheet I'm into a new workbook.

For example, when the user clicks the "Save Worksheet" Macro button, it'll save the worksheet they're in to a new workbook with

1. The new workbook named with the "old sheet name" + what information is in cell "N2" + "N5"

Any help would very much be appreciated!

-Thank you

Hussain

 

[Joe4]

Hi rlv01,

WOW! That actually worked perfect!

Curious, just a question to follow, how would I specify a range for the printout?

For example, range A1:AY38?

Thanks,
pinaceous

One simple way is to copy just the range that you want to a new sheet, and save that sheet instead.

 

[rlv01]

Curious, just a question to follow, how would I specify a range for the printout?

For example, range A1:AY38?

The method mentioned by @Joe4 is pretty easy:

One simple way is to copy just the range that you want to a new sheet, and save that sheet instead.

You can also use the Range PrintOut method.

Dim rngPrint As Range
Set rngPrint = Range("A1:AY38")
rngPrint.PrintOut

 

[Pinaceous]

The method mentioned by @Joe4 is pretty easy:


You can also use the Range PrintOut method.

Dim rngPrint As Range
Set rngPrint = Range("A1:AY38")
rngPrint.PrintOut

I tried something like this but it did not work, can you take a look at it?

Sub SveShts()

Dim rngPrint As range
Set rngPrint = range("A1:AY38")
rngPrint.PrintOut
    
    Dim xPath As String
    Dim xWs  As String
    Dim xFilename As String
    
    xPath = Application.ActiveWorkbook.path
    
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    With ActiveSheet
        ActiveSheet.Copy
        xFilename = xPath & "\" & ActiveSheet.Name & " " & Sheet4.range("A24").Value & " " & ".txt"
        
        Application.ActiveWorkbook.SaveAs filename:=xFilename, FileFormat:=xlText
        Application.ActiveWorkbook.Close False
    End With
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    

    
End Sub

Thank you!
pinaceous

 

[rlv01]

I tried something like this but it did not work, can you take a look at it?

Without details and explanatory text, "Did not work" can mean a wide range of things, and I am bad at guessing.

 

[Pinaceous]

I tried something like this but it did not work, can you take a look at it?

Sub SveShts()

Dim rngPrint As range
Set rngPrint = range("A1:AY38")
rngPrint.PrintOut
   
    Dim xPath As String
    Dim xWs  As String
    Dim xFilename As String
   
    xPath = Application.ActiveWorkbook.path
   
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    With ActiveSheet
        ActiveSheet.Copy
        xFilename = xPath & "\" & ActiveSheet.Name & " " & Sheet4.range("A24").Value & " " & ".txt"
       
        Application.ActiveWorkbook.SaveAs filename:=xFilename, FileFormat:=xlText
        Application.ActiveWorkbook.Close False
    End With
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   

   
End Sub

Thank you!
pinaceous

Hi rlv01,

When I add

Dim rngPrint As range
Set rngPrint = range("A1:AY38")
rngPrint.PrintOut

It goes directly to print and doesn't allow the notepad to be created. What I expected was for my range of "A1:AY38" to be created into the notepad and not for any printouts to be created from the sub.

Can you kindly help me to adjust the code:

Sub SveShts()

   
    Dim xPath As String
    Dim xWs  As String
    Dim xFilename As String
   
    xPath = Application.ActiveWorkbook.path
   
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    With ActiveSheet
        ActiveSheet.Copy
        xFilename = xPath & "\" & ActiveSheet.Name & " " & Sheet4.range("A24").Value & " " & ".txt"
       
        Application.ActiveWorkbook.SaveAs filename:=xFilename, FileFormat:=xlText
        Application.ActiveWorkbook.Close False
    End With
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   

   
End Sub

To produce the notepad but only in the

range("A1:AY38")

Upon that sheet.

Please let me know.

Thank you!
pinaceous

 

[Logit]

Everything located on the internet indicates Microsoft removed 'print preview' from the recent versions of Notepad.

Wordpad can provide the preview function.

 

[rlv01]

..how would I specify a range for the printout?

You have to careful what words you use when you ask for help. Earlier you asked for help to specify a range for "printout", and that word has a very specific meaning - which is to send something to a printer. If you just want to specify a range to save, then best to copy it to a new workbook to save.

Sub SveShts()
    Dim xPath As String
    Dim xFilename As String
    Dim WB As Workbook
    Dim rngCopy As Range
    
    Set rngCopy = ActiveSheet.Range("A1:AY38")
    
    xPath = ActiveWorkbook.Path
    xFilename = xPath & "\" & ActiveSheet.Name & " " & Sheet4.Range("A24").Value & " " & ".txt"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set WB = Workbooks.Add
    With WB.Worksheets(1)
        rngCopy.Copy .Range("A1")
        .UsedRange.Columns.AutoFit
    End With
    WB.SaveAs Filename:=xFilename, FileFormat:=xlText
    DoEvents
    WB.Close False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[Pinaceous]

You have to careful what words you use when you ask for help. Earlier you asked for help to specify a range for "printout", and that word has a very specific meaning - which is to send something to a printer. If you just want to specify a range to save, then best to copy it to a new workbook to save.

Sub SveShts()
    Dim xPath As String
    Dim xFilename As String
    Dim WB As Workbook
    Dim rngCopy As Range
   
    Set rngCopy = ActiveSheet.Range("A1:AY38")
   
    xPath = ActiveWorkbook.Path
    xFilename = xPath & "\" & ActiveSheet.Name & " " & Sheet4.Range("A24").Value & " " & ".txt"
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Set WB = Workbooks.Add
    With WB.Worksheets(1)
        rngCopy.Copy .Range("A1")
        .UsedRange.Columns.AutoFit
    End With
    WB.SaveAs Filename:=xFilename, FileFormat:=xlText
    DoEvents
    WB.Close False
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Hi rlv01,

Yes your absolutely correct! I don't know what I don't know and I recognize that I'm a work in progress.

Thank you very much for the code and for helping me out! Let me give it a go!

Respectfully,
pinaceous