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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.