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]

You can't save a sheet by itself, only a workbook. An option is to copy the sheet to a new workbook and save the new workbook.

 

[orsm6]

You can't save a sheet by itself, only a workbook. An option is to copy the sheet to a new workbook and save the new workbook.

ahh ok, that makes sense. Thanks Mark I'll look into changing my approach.

 

[MARK858]

Thanks Mark I'll look into changing my approach.

All you need to do is add the line

 Worksheets("Lists").Copy

above the line below

ActiveWorkbook.SaveAs savepath & "\" & Fname

 

[orsm6]

All you need to do is add the line

 Worksheets("Lists").Copy

above the line below

ActiveWorkbook.SaveAs savepath & "\" & Fname

thank you sir....

 

[orsm6]

All you need to do is add the line

 Worksheets("Lists").Copy

above the line below

ActiveWorkbook.SaveAs savepath & "\" & Fname

actually... when i did that - i get the prompt to continue to save as a macro enabled workbook or not. is there a way around this to 'default' saving as a macro book or user has to click yes?

the macro then crashes into debug.

 

[MARK858]

I don't get that prompt (and I wouldn't until the next line in the code).

What I did need to do is
a) remove the bit in red below as you are already adding a "\" at the end of

savepath = savepath & Fname & "\"

ActiveWorkbook.SaveAs savepath & "\" & Fname

b) I added the fileformat (i.e. the 52 below) so it ended up as

    Worksheets("Lists").Copy
    ActiveWorkbook.SaveAs savepath & Fname, 52

 

[orsm6]

I don't get that prompt (and I wouldn't until the next line in the code).

What I did need to do is
a) remove the bit in red below as you are already adding a "\" at the end of

savepath = savepath & Fname & "\"

ActiveWorkbook.SaveAs savepath & "\" & Fname

b) I added the fileformat (i.e. the 52 below) so it ended up as

    Worksheets("Lists").Copy
    ActiveWorkbook.SaveAs savepath & Fname, 52

thanks Mark.... do i only remove the "\" from the ActiveWorkbook line?

i have only changed that one and the macro now works.
the only reason i ask, is for some reason when i try to open the new file created in the new folder it says the file doesn't even exist. And it seems that I can't delete the folder that the macro created haha.



so i am not sure if this is a macro issue or an IT thing.

 

[MARK858]

Comment out the

ActiveWorkbook.SaveAs savepath & Fname, 52

and replace it with

Debug.Print savepath & Fname

Then run your code and check in the Immediate window if the filepath looks correct.

 

[orsm6]

Comment out the

ActiveWorkbook.SaveAs savepath & Fname, 52

and replace it with

Debug.Print savepath & Fname

Then run your code and check in the Immediate window if the filepath looks correct.

creates folder.... creates new workbook (book1) does not go all the way thru to save it with filename etc.