PDA

View Full Version : Solved: Save a sheet in workbook to a specific folder automatically



ctuna
02-11-2008, 07:35 PM
I have a work book that has several sheets in it. One of the sheets is a packing list sheet. When I ship equip. out it is listed on this sheet along with where it is going.

My question is can I save this sheet to a specific folder by a specifc name automatically when I close the workbook.

shasur
02-12-2008, 05:31 AM
Here is a hint

This uses Workbook_BeforeClose event to save the Equip Sheet. I am appending the date to the file name


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sDate As String
sDate = Replace(Date, "/", "-")
Sheets("Equip").Copy
ActiveWorkbook.SaveAs Filename:="C:\Equip" & sDate & ".xls", FileFormat:=xlNormal
End Sub

ctuna
02-12-2008, 07:28 AM
This is the sheet I am trying to save. I would like to save it in my documents in the packing slips folder. I would also like to save it by the randon number that is generated each time the sheet is opened.

:banghead:

ctuna
02-13-2008, 07:05 AM
Shasur;
I can't seem to get it to work. Any other suggestions.

Thank You

Bob Phillips
02-13-2008, 08:50 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Filename As String

With ThisWorkbook

Filename = "C:\Packing Slips\" & Replace(.Name, ".xls", " ")
.SaveCopyAs Filename & Sheets(1).Range("H47").Value & ".xls"
End With
End Sub

ctuna
02-14-2008, 06:22 AM
Guys I really appreciate the help, but it's still not working for me. What else can I do. Or what am I doing wrong.
I go to view code then I copy the text in the sheet code, but it will not save it.
Please let me know what I am doing wrong.

Thank You!!!!!!:dunno

Bob Phillips
02-14-2008, 06:40 AM
More detail. AN error message, nothing happemns, it saves somewhere else?

may a sample workbook.

ctuna
02-14-2008, 06:57 AM
No error, nothing. It doesn't seem to be saving any where else. Even the attachment in the above post doesn't do it, but I didn't see any code in it.:bug:

Bob Phillips
02-14-2008, 07:22 AM
I just tried it and it saved it fine for me, so I am really confused.

is H47 getting updated when you open it?

ctuna
02-14-2008, 08:15 AM
H 47 is updating. Where is it saving for you?

Bob Phillips
02-14-2008, 08:38 AM
C:\Packing Slips

ctuna
02-14-2008, 11:24 AM
Maybe this will help. This is the whole workbook. Maybe I'm missing something.



Sorry can't upload it.

Bob Phillips
02-14-2008, 11:28 AM
Zip it or post it somewhere else on the web.

ctuna
02-14-2008, 11:40 AM
Thank you xld. Here we go let's try this.

Bob Phillips
02-14-2008, 12:12 PM
You put the code in the userform code module, goodness knows why, but you shoould have put it in the ThisWorkbook code module.

ctuna
02-14-2008, 01:23 PM
Getting closer :think:
When I close it, it says Run Time Error 1004

When I debug it this line is highlighted
.SaveCopyAs Filename & Sheets(1).Range("H47").Value & ".xls"

ctuna
02-14-2008, 02:38 PM
Got it working

1 minor problem. It's saving the whole workbook instead if just the packing slips.

any suggestions

Bob Phillips
02-14-2008, 03:03 PM
The workbook you posted was only the single sheet.

ctuna
02-14-2008, 03:17 PM
The zip file was several sheets

Bob Phillips
02-14-2008, 03:26 PM
My code was based on the previous xls.



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Filename As String

With ThisWorkbook

.Worksheets("Packing Slips").Copy
Filename = "C:\Packing Slips\" & Replace(.Name, ".xls", " ")
End With

With ActiveWorkbook

.SaveCopyAs Filename & Sheets(1).Range("H47").Value & ".xls"
.Close
End With
End Sub

ctuna
02-14-2008, 08:18 PM
The one above works, but when you exit it ask if, where and what you want to save it as.It comes up as book 1 and if you don't change it, it overites the previous one

This code works fine. The only problem with it is it saves the whole workbook, not just the file.



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Filename As String

With ThisWorkbook

Filename = "C:\Documents and Settings\All Users\Documents\Inventory" & Replace(.Name, ".xls", " ")
.SaveCopyAs "C:\Packing Slips\Packing Slips" & Sheets(7).Range("H47").Value & ".xls"
End With
End Sub

ctuna
02-15-2008, 06:37 AM
Any suggestions on how to stop from saving whole woorbook?:dunno

Bob Phillips
02-15-2008, 06:42 AM
I meant my code to be



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Filename As String

With ThisWorkbook

.Worksheets("Packing Slips").Copy
Filename = "C:\Packing Slips\" & Replace(.Name, ".xls", " ")
End With

With ActiveWorkbook

.SaveAs Filename & Sheets(1).Range("H47").Value & ".xls"
.Close
End With
End Sub

ctuna
02-15-2008, 07:25 AM
xld - YOU ARE THE MAN:clap: