Saving Just One Sheet?

Re: Saving Just One Sheet?

Try this.

Dim wb As Workbook


Worksheets("Sheet1").Copy
Set wb = ActiveWorkbook


wb.SaveAs "New Workbook.xls"


wb.Close

Re: Saving Just One Sheet?

Hi,
I am using this code with a workbook I created, but I have several sheets,
one being Quotation, and the other invoice.
When I use this code to save i want it to look at the reference number which is G4 and add this to the end of the quote in the quote sheet and invoice for the invoice sheet.
Can you please help

Re: Saving Just One Sheet?

Hi guggu,

wb.SaveAs "New Workbook " & Range("G4") & ".xls"

Stefan

Re: Saving Just One Sheet?

Quote from StefanG

Hi guggu,

Code

wb.SaveAs "New Workbook " & Range("G4") & ".xls"

Stefan

Thanks with a bit of more twinkering I created a folder and got it to save there.
Many thanks

Re: Saving Just One Sheet?

Sub Save_Quote()
Dim wb As Workbook
 


Worksheets("Sheet3").Copy
Set wb = ActiveWorkbook


MkDir ("C:\Dynamite\Quote")
ChDir ("C:\Dynamite\Quote")
 
wb.SaveAs "Quote " & Range("G4") & ".xls"
 
wb.Close
End Sub

I think I have hick up here.
The macro I have is on Sheet 2 but the sheet I want to save is Sheet 3,
What do I need to change here ?

Re: Saving Just One Sheet?

Your code should be in a standard module, not the worksheet.

Re: Saving Just One Sheet?

Hi there!

I'm playing with the code above (from guggu). How can I specify the currently active sheet?

Thanks!

Re: Saving Just One Sheet?

Hi sweather,
change

Worksheets("Sheet3").Copy

to

ActiveSheet.Copy

Stefan

Re: Saving Just One Sheet?

Thanks for the fast reply Stefan!

I'm still running into a bit of a problem, though. I'm getting the error:
Run Time Error 75
Path/File Access Error

Here's the code I have:

Dim wb As Workbook
     
     
    ActiveSheet.Copy
    Set wb = ActiveWorkbook
     
    MkDir ("C:\CharterWorkbook")
    ChDir ("C:\CharterWorkbook")
     
    wb.SaveAs "Report " & Range("F1") & Range("F2") & ".xls"
     
    wb.Close

Oddly enough, it created the folder exactly where it was supposed to -- it just didn't put the sheet that I wanted inside it! Actually, it didn't put ANYthing in there! lol

Any ideas?

Re: Saving Just One Sheet?

Hi sweather,

Quote

Run Time Error 75

I believe that is because the directory/folder "CharterWorkbook" already exists.
Among other options (check the forum here for these), i suppose, you could change

MkDir ("C:\CharterWorkbook")

to

On Error Resume Next
MkDir ("C:\CharterWorkbook")
On Error GoTo 0

Stefan

Re: Saving Just One Sheet?

Thanks Stefan!
That did the trick to fix the error I was getting! :music:

However, I'm still kinda stumped...

The code creates the folder like it should, where it should, but doesn't put anything into it. When I run the code, I get a popup advising me that a file by that name already exists in that location, and wanting to know if I want to replace it or not. But, there's no file there! :confused:

Even after I choose 'Yes' to replace the file-that-it-thinks-is-there-but-isn't-really-there, there's still no file in there!! lol

Freaky, and unfortunately, I have to run home now, so I'll pick up the fight again when I get back to the office tomorrow.

Thanks tons for all of your help on this!!!!!!!!!

Re: Saving Just One Sheet?

Hi,
I'm not sure why it would tell you the file already exists...
Try this and see if it still errors.

Dim wb As Workbook
Dim MyStr
Set wb = ActiveWorkbook
MyStr = Format(Time, "hh-mm-ss")
ActiveSheet.Copy
On Error Resume Next
MkDir ("C:\CharterWorkbook")
On Error GoTo 0
ChDir ("C:\CharterWorkbook")
wb.SaveAs "Report " & Range("F1") & Range("F2") & " " & MyStr & ".xls"
wb.Close

It basically adds the current time (hh-mm-ss) to the file name, which makes it nearly impossible to duplicate a file name. I wonder if you'll still get the error?¿
Stefan

Re: Saving Just One Sheet?

You should not rely on ChDir, you should specify the directory to save to.

Dim wb As Workbook 
Dim MyStr 


MyStr = Format(Time, "hh-mm-ss") 
ActiveSheet.Copy 
Set wb = ActiveWorkbook 
MkDir ("C:\CharterWorkbook") 
wb.SaveAs "C:\CharterWorkbook\Report " & Range("F1") & Range("F2") & " " & MyStr & ".xls" 
wb.Close

Re: Saving Just One Sheet?

Excellent work, guys!!

Just one more little issue, and I think this one will be all wrapped up:

When I run the code on a sheet, it does exactly what it should. However, if I go and run it again a few minutes later, I get an Error 75 again (Path/File access error). When I click on the 'Debug' option in the error window, the VB Editor opens and has this line highlighted:

MkDir ("C:\CharterWorkbook")

Am I getting this error because it's trying to create a directory that already exists? If so, is there any way to make the code use this directory if it's already there, rather than trying to re-create one with the same name?

Again, thanks for all the guidance!! :music:

Re: Saving Just One Sheet?

hi,

this code checks if a directory exists

If Len(Dir("C:\CharterWorkbook", vbDirectory)) > 0 Then 
Msgbox "Directory Exists"
End If

Re: Saving Just One Sheet?

i think you can apply it to yr code this way:

Dim wb As Workbook
Dim MyStr
 
MyStr = Format(Time, "hh-mm-ss")
ActiveSheet.Copy
Set wb = ActiveWorkbook


If Not Len(Dir("C:\CharterWorkbook", vbDirectory)) > 0 Then 'if dir does not exist
MkDir ("C:\CharterWorkbook")
End If


wb.SaveAs "C:\CharterWorkbook\Report " & Range("F1") & Range("F2") & " " & MyStr & ".xls"
wb.Close

Re: Saving Just One Sheet?

WICKED!!! It's working perfectly!!!

Thanks so much to xlite, norie, and stefan!!

Consider this one solved!!