macro to 'save as' using info from two cells.

[abagnall]

im wanting a button/macro that when pressed will auto save as the workbook, combining data from two cells on the work sheet.

the code ive wrote, brings a pop up box with "400" wrote in it and the options of ok and help (help doesnt help!)

Sub Button1_Click()
Dim Char As Variant
Dim FilePath As String
Dim FileType As String
Dim NewName As String

FileType = ".xlsx"

For Each Char In Array("<", ">", "?", "/", "\", ":", "*", """")
Ans = Replace(Ans, Char, "_")
Next Char
NewName = [f4] & "-" & [f3]
If InStrRev(Ans, ".") = 0 Then NewName = NewName & FileType

ChDir _
"C:\"
ActiveWorkbook.SaveAs Filename:=NewName
End Sub


can any body who knows more about this than me point out where im going wrong?

 

[Joe4]

What is in cells F4 and F3?
Whnat is your loop supposed to be doing?

 

[Jugantarji]

If you can Concatenate on the sheet in a hidden row/column then you can use

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

It needs to be saved as a module rather than on the sheet

 

[abagnall]

What is in cells F4 and F3?

F4 is a name (its drop down list, linked to data in another workbook. F3 is the date, based on a vlookup related to the name chosen from the dropdown (again data taken from another workbook)

as for the loop youve mentioned, im not sure what it is looping...... the macro is what ive tried to peice together from what ive read on the web, id be lieing if i said i fully understood it. its why im here, talking to much smarter people than myself.

 

[Joe4]

You will need to include a file path in your file name. So if F3 and F4 do not include those, you have some work to do there.
Depending on what your values in F3 and F4 look like (you haven't provided any examples), that loop may be unnecessary. As it is currently written, I don't think it will work anyway.

It might be helpful too if you provide links to the places where you got your code.

 

[abagnall]

in F4 - the formula bar just displays the "band" name, picked from a drop down, this works fine. the list is update when other people add extra bands on the other work book. i forget how i managed this, but it included named ranges and DV.......it seems to be working the way i want, even if im not sure how

F3 - has this formula " =VLOOKUP(F4,ShowSales2013.xlsx!showdata,MATCH("date",ShowSales2013.xlsx!title,0),0) " again this updates fine whenever a new band is picked from the drop down, the date is updated too.

i did manage a macro that did save from just one cell, but it filled any spaces in the words with %.

ill check my history and see if i can find the sites that i "borrowed" the codes from.

thanks for this mate.

 

[Joe4]

I would recommend temporarily commenting out the save statement and replacing it with a Message Box at first:

MsgBox NewName
''ActiveWorkbook.SaveAs Filename:=NewName

Then when you run it, it will pop-up a Message Box with the file name that it wants to save. Use this method for testing, until you have your code working correctly. Once you do, then you can remove it and uncimment your Save statement.

The goal is to get your Message Box to return a valid file path and file name, i.e.
C:\MyFiles\MyFileName.xls

 

[abagnall]

If you can Concatenate on the sheet in a hidden row/column then you can use

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

It needs to be saved as a module rather than on the sheet

ive gone for this options and its working well. so thank you. thank all of you! my little spread sheet is coming along nicely down to all the great people on this site.