save sheet only

[orsm6]

hi all - my macro works well and saves the entire workbook, but i need it to save only either the active sheet or sheet named PCR Form. how can i modify the code below? i tried changing to ActiveSheet but it still saved the whole book.

Option Explicit
Sub SaveFile()
    Dim savepath As String
    Dim Fname As String
    Fname = Worksheets("Lists").Cells(1, 10).Value
    savepath = "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\Submitted PCRs\"
    savepath = savepath & Fname & "\"
    If Dir(savepath, vbDirectory) = "" Then
        MkDir savepath
    End If
    Fname = Fname & " " & Format(Now(), "dd.mmm.yy hhmm AMPM") & ".xlsm"
    ActiveWorkbook.SaveAs savepath & "\" & Fname

'removes upload button after the first time the PCR is submitted and saved
ActiveSheet.Shapes("Picture 36").Visible = False
ActiveSheet.Shapes("TextBox 37").Visible = False

'shows save icon after first upload to register
ActiveSheet.Shapes("Picture 43").Visible = True
ActiveSheet.Shapes("TextBox 49").Visible = True

TIA

 

[MARK858]

With J1 on sheet Lists containing the value abcd the code below creates a folder on my desktop called abcd and within that folder a workbook called abcd 15.Jul.20 0113 AM.xlsm i.e.
C:\Users\MARK858\Desktop\abcd\abcd 15.Jul.20 0113 AM.xlsm

Sub SaveFile()
    Dim savepath As String
    Dim Fname As String
    Fname = Worksheets("Lists").Cells(1, 10).Value
    savepath = "C:\Users\MARK858\Desktop\"
    savepath = savepath & Fname & "\"
    If Dir(savepath, vbDirectory) = "" Then
        MkDir savepath
    End If
    Fname = Fname & " " & Format(Now(), "dd.mmm.yy hhmm AMPM") & ".xlsm"
    ActiveWorkbook.SaveAs savepath & Fname, 52

'removes upload button after the first time the PCR is submitted and saved
'ActiveSheet.Shapes("Picture 36").Visible = False
'ActiveSheet.Shapes("TextBox 37").Visible = False
'
''shows save icon after first upload to register
'ActiveSheet.Shapes("Picture 43").Visible = True
'ActiveSheet.Shapes("TextBox 49").Visible = True
End Sub

 

[orsm6]

With J1 on sheet Lists containing the value abcd the code below creates a folder on my desktop called abcd and within that folder a workbook called abcd 15.Jul.20 0113 AM.xlsm i.e.
C:\Users\MARK858\Desktop\abcd\abcd 15.Jul.20 0113 AM.xlsm

Sub SaveFile()
    Dim savepath As String
    Dim Fname As String
    Fname = Worksheets("Lists").Cells(1, 10).Value
    savepath = "C:\Users\MARK858\Desktop\"
    savepath = savepath & Fname & "\"
    If Dir(savepath, vbDirectory) = "" Then
        MkDir savepath
    End If
    Fname = Fname & " " & Format(Now(), "dd.mmm.yy hhmm AMPM") & ".xlsm"
    ActiveWorkbook.SaveAs savepath & Fname, 52

'removes upload button after the first time the PCR is submitted and saved
'ActiveSheet.Shapes("Picture 36").Visible = False
'ActiveSheet.Shapes("TextBox 37").Visible = False
'
''shows save icon after first upload to register
'ActiveSheet.Shapes("Picture 43").Visible = True
'ActiveSheet.Shapes("TextBox 49").Visible = True
End Sub

yep that works for me also.... i think i see why i am having issues with reopening the new file in the new saved folder.... there is a space after the directory name.... between the name and the \... so example PCR20019 \ and it should be PCR20019\

 

[MARK858]

Remember that my last code does not have the Worksheets("Lists").Copy line that you need to add in.

 

[orsm6]

Remember that my last code does not have the Worksheets("Lists").Copy line that you need to add in.

Oh quite right Mark..... i did miss that, thank you.

and thank you very much for lending your skills, this now works perfectly.

 

[MARK858]

You're welcome

 

[orsm6]

You're welcome

Hi Mark - I was having a play with this code and the only thing i thought i had done was change it to save as a pdf.....

so the code runs just fine, but when someone else runs the macro we are unable to delete the folder that was made from the path. seem to be able to delete the folder contents fine though.
are you able to help with this or should i start a new thread??

Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

    strName = Worksheets("PCR Form").Cells(1, 12).value
   
'check if the PCR folder exists, if it doesn't make one
    savepath = "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\PCRs\"
    savepath = savepath & strName & "\"
    If Dir(savepath, vbDirectory) = "" Then
        MkDir savepath
    End If

'define where the PCRs are saved on S drive
    strPath = "\\mdzausutwfnp001\Shardata\Change Management\Plant Change Requests\PCRs\"
    strPath = savepath & "\"

'create default name for savng file
strFile = strName & " " & Format(Now(), "dd.mmm.yy hhmm AMPM") & ".pdf"
strPathFile = strPath & strFile

'export to PDF in current folder
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    'confirmation message with file info
    MsgBox "PDF file has been created: It is saved in Change Management\PCRs folder" _
      '& vbCrLf _
      '& strPathFile

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

if i run the code myself i am able to delete the contents and the folder that was created.

 

[MARK858]

are you able to help with this or should i start a new thread??

Better to start a new thread as it is a different question and I am at work today.

Edit: OP has already started a new thread here

 

[orsm6]

Better to start a new thread as it is a different question and I am at work today.

Edit: OP has already started a new thread here

yeah i had a think about it, and thought it should have been a new thread so made one as you saw.