Solved: Macro will not save

[Meatball]

I have this posted to another forumn but have received no solution there. I am not able to find rules on multi forumn posting for this site.
I have tried several different methods to make this code save a file but have had no success. Thanks in advance to anyone here who can help. This is a recorded macro with modifications.

Sub Exacta()
'
' Exacta Macro
' Macro recorded 2/27/2009 by David D
'
'

Workbooks.Open Filename:="Z:\Gould Southern Info\GA Kits\Forms & Templates\RFQ ---- to EX.xls"
ThisWorkbook.Activate
Sheets("RFQ to EX BOM Pg 2").Select
Range("A15:F" & Range("B2000").End(xlUp).Row).Select
Selection.Copy
Windows("RFQ ---- to EX.xls").Activate
Sheets("BOM").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A2").Select
ThisWorkbook.Activate
Range("A2").Select
Sheets("RFQ to Exacta PG 1").Select
Columns("A:H").Select
Application.CutCopyMode = False
Selection.Copy
Windows("RFQ ---- to EX.xls").Activate
Sheets("Cover").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Workbooks.Open Filename:="Z:\Gould Southern Info\GA Kits\Forms & Templates\RFQ ---- Austin Foam.xls"
ThisWorkbook.Activate
Sheets("Foam Cost").Select
Range("A1:H22").Select
Application.CutCopyMode = False
Selection.Copy
Windows("RFQ ---- Austin Foam.xls").Activate
Sheets("Sheet1").Select
Range("b33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Run "AustinSaveAs()"
Range("d56:d59").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Foam Cost").Select
Range("c24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



End Sub
Sub AustinSaveAs()
ChDir _
"Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ " & Range(c34).Value & Range(c37).Value
WB.SaveAs Filename:= _
"Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ " & Range(c34).Value & Range(c37).Value

End Sub

[lucas]

Well, for one thing, how is excel supposed to know what WB is.
Sub AustinSaveAs()
ChDir _
"Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ " & Range(c34).Value & Range(c37).Value
WB.SaveAs Filename:= _
"Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ " & Range(c34).Value & Range(c37).Value

End Sub

[lucas]

See if this give you any clues:
Dim DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
ActiveWorkbook.SaveAs DTAddress & Sheets("Details").Range("R2")

[Meatball]

Thanks for the help Lucas.
WB was the last example that I ran across and tried. As for your code, I do not quite understand all that it is suppossed to do but that is nothing new.
I am thinking that it goes in the Sub AustinSaveAs() but is it replacing the code in the sub or is it an addition to the sub?

[lucas]

Well, I saw no place in the other routine that was saving a file so yes, it was for the austinsavas macro and to replace the code that is in it now.

Does the original austinsavas macro work if you assign a workbook to WB or replace WB with thisworkbook?

[Meatball]

Using Workbook instead of WB did not work.

[Meatball]

Tried it, does not seem to work. The Austin file that is opened does not change names and I can not find a new file anywhere.

[lucas]

To get it to working so you can tinker with it try this: You will have to change the path or add a directory under C:\ called Temp.


Sub AustinSaveAs()
ActiveWorkbook.SaveAs Filename:="C:\Temp\ " & Range("c34").Value & Range("c37").Value
End Sub


then you can work on changing the path in the code. You weren't using "c34" in the range calls for one thing.

[Meatball]

I alraedy had a temp folder in C:\ so I tied it as is and still no luck

[Meatball]

Well, I moved the save file up into the code and it will save if the name is already specified but when I try to change it to use cells from the file to make the name then the macro stops working.


Sub Exacta()
'
' Exacta Macro
' Macro recorded 2/27/2009 by David D
'
'

Workbooks.Open Filename:="Z:\Gould Southern Info\GA Kits\Forms & Templates\RFQ ---- to EX.xls"
ThisWorkbook.Activate
Sheets("RFQ to EX BOM Pg 2").Select
Range("A15:F" & Range("B2000").End(xlUp).Row).Select
Selection.Copy
Windows("RFQ ---- to EX.xls").Activate
Sheets("BOM").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A2").Select
ThisWorkbook.Activate
Range("A2").Select
Sheets("RFQ to Exacta PG 1").Select
Columns("A:H").Select
Application.CutCopyMode = False
Selection.Copy
Windows("RFQ ---- to EX.xls").Activate
Sheets("Cover").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ThisWorkbook.Activate
Workbooks.Open Filename:="Z:\Gould Southern Info\GA Kits\Forms & Templates\RFQ ---- Austin Foam.xls"
ThisWorkbook.Activate
Sheets("Foam Cost").Select
Range("A1:H22").Select
Application.CutCopyMode = False
Selection.Copy
Windows("RFQ ---- Austin Foam.xls").Activate
Sheets("Sheet1").Select
Range("b33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.SaveAs Filename:= _
"Z:\Gould Southern Info\GA Kits\Forms & Templates\RFQ XXXX Austin Foam .xls"
Range("d56:d59").Select
Application.CutCopyMode = False
Selection.Copy
ThisWorkbook.Activate
Sheets("Foam Cost").Select
Range("c24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



End Sub

[lucas]

Well this file saves to c:\Temp for me as the values in the cells.

Maybe your c:\Temp has a small t in temp or something. Try it and see if it helps.

edit: I can't upload a file right now for some reason but it is the same code I had in the other post:

Sub AustinSaveAs()
ActiveWorkbook.SaveAs Filename:="C:\Temp\ " & Range("c34").Value & Range("c37").Value
End Sub

[Meatball]

That's not it. Big T in Temp folder. Thanks for trying. I am going to give it a rest for now. Maybe something will come to me over the weekend that will push in the right direction.

[lucas]

Ok, you opened a new workbook, put the code I provided in a standard module, made sure there was text in the two cells referenced in the code and you have a C:\Temp directory and this doesn't work?

Recheck your steps because it definatly works for me on this end.

[mdmackillop]

Try this (untested)

Sub Exacta()

Dim WB As Workbook, WBtoSave As Workbook
Dim cel As Range

Set WB = Workbooks("RFQ ---- to EX.xls")
Set cel = ActiveCell

Workbooks.Open Filename:="Z:\Gould Southern Info\GA Kits\Forms & Templates\RFQ ---- to EX.xls"
Sheets("RFQ to EX BOM Pg 2").Range("A15:F" & Range("B2000").End(xlUp).Row).Copy

cel.PasteSpecial Paste:=xlPasteValues
cel.PasteSpecial Paste:=xlPasteFormats
WB.Sheets("RFQ to Exacta PG 1").Columns("A:H").Copy
WB.Sheets("Cover").Range("A1").PasteSpecial Paste:=xlPasteValues

Workbooks.Open Filename:="Z:\Gould Southern Info\GA Kits\Forms & Templates\RFQ ---- Austin Foam.xls"
Sheets("Foam Cost").Range("A1:H22").Copy
Sheets("Sheet1").Range("b33").PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.SaveAs "Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ " & Range("c34").Value & Range("c37").Value

Range("d56:d59").Copy
Sheets("Foam Cost").PasteSpecial Paste:=xlPasteValues

End Sub

[lucas]

Hi Malcolm, I think that this will not work without "s in the cell range callouts will it?

ActiveWorkbook.SaveAs "Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ " & Range(c34).Value & Range(c37).Value

[mdmackillop]

Thanks Steve, I did add them then changed my mind and copied the OP's code.

[Meatball]

Thanks for trying mdm. I get a run time error #9, Subscript out of range.
The line "Set WB = Workbooks("RFQ ---- to EX.xls")" is highlighted.

[mdmackillop]

The workbook with that name must be open (and spelt correctly in the code!), otherwise, to open the book
Set WB = Workbooks.Open("C:\AAA\RFQ ---- to EX.xls") or whatever.

[Meatball]

I have tried this a hundred different ways and still no luck. I gave up a while back on running the 2 subs together and have tried to get a code that will just save the workbook. 50 different tries on that and no luck.
I think it may be time to give a very detailed explanation of what I want to happen.
For now I will put this macro in the file which is opened by existing code. If I can get the macro to save the file that is opened like I want I will try to incorporate it into the file that actually opens the new file.
Sub will be named "SaveExacta".
The path for the folder it will save to is "Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ " and on sheet "Cover" the cells C2 (which is the RFQ #), & " " & cell F2 (which is the customer name) & "-" (what we use to seperate the customer name and project name) & C3 which is the project name, and " to Ex" and the date(dd/mm/yy). An example of a full folder name would be "Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ 22334 Bill's Metal-Cover Plate". The name of the file when saved would be "RFQ 22334 Cover Plate to EX 3-12-09.xls". The word Manifest is always in the name of the file but the customer name is not, the customer name can be included if it makes the code easier to write. So as iI do it now the full file name would be "Z:\Gould Southern Info\GA Kits\Quotes in Process\RFQ 22334 Bill's Metal-Cover Plate\RFQ 22334 Cover Plate to Ex 3-12-09.xls".
"Z:\" is a server which requires a password to access but once on the password is not needed to save files, etc. At least 1 of my attemps did save to the server but the wrong file(saved the file running the macro, not the file I was trying to save) and in the wrong place so I do not believe the password to be an issue.
I hope this will be enough info to figure out why previous suggestions have not worked.

[lucas]

This file saves a copy to C:\Temp for me. If it doesn't on your machine then I don't know why.

[lucas]

I have this posted to another forumn but have received no solution there. I am not able to find rules on multi forumn posting for this site.


Post a link to it in a post here please:

http://www.excelguru.ca/node/7

[Meatball]

Yes, I can save a file using that but when I try to adjust it to fit my situation I start getting errors.

[lucas]

Maybe you need to qualify which sheet the ranges are on. Are you activating the sheet with the ranges that you want in the filename before you try to save it?

[Meatball]

Well, I do not know how it happened but I made it work. If I actually knew what I was doing I might be able to say, as it is I just tripped into it. Anyway, Many, Many Thanks to everybody for all the time and effort. Now Ijust have one small thing to madd. I would like to have the date of creation added to the end of the file. Thank you in advance for the date thing. I will now mark this as solved!

[lucas]

I don't know what you mean by end of file. You will have to be more specific. Do you mean end of file name? In a cell?

Sub Test()
Dim szCreated As String
szCreated = ThisWorkbook.BuiltinDocumentProperties("Creation Date")
MsgBox szCreated
End Sub

[mdmackillop]

Hi Steve,
I thing he means adding to the file name during save. If so, i would add it in "yymmdd" format which allows sorting by date order

[lucas]

That would make more sense Malcolm. Maybe the op can clear it up. Either way, not a big deal.

[Meatball]

mdm is correct, add to the end of the file name. I thought it would be....& date("mm/dd/yy").... but it seems there needs to more to it. I can't quite pin down the exact way to write it.

[lucas]

More like this:

ActiveWorkbook.SaveAs Filename:="C:\Temp\ " & Range("c34").Value & " " & Range("c37").Value & "-" & Format$(Date, "mm-dd-yy")

[Meatball]

That did it. Thanks to both of you for the help with this. I am sure we will be in touch over future projects of mine.

[BrianMH]

Out of curiosity what does the $ do after the format? I use format quite often and have never used the $ so am curious as to what it does?

[mdmackillop]

Out of curiosity what does the $ do after the format? I use format quite often and have never used the $ so am curious as to what it does?
http://support.microsoft.com/kb/191713