save a copy of a workbook -vba

[avishain]

hi,

im usnig excel 2007.

i want to create a button which will do the following:

1.once i click it- it will pop up a window and ask me to fill in a number ("pls enter your number").

i need "ok" and "cancel" option (if i hit the "cancel" button so nothing happend and im back to my excel sheet).

2.if i pressed "ok" so excel will show me my folders on my computers and there i want to save the copy.

3. this copy will have the name according to what i have entered (the number).


for instance,if excel askes me -("pls enter your number") and i typed 1111

so eventually ill have a copy named 1111.xlsx in the spesific folder the i chose before.


thanks u very much

 

[Trevor G]

You need something along these lines. Not checked

Sub copyWorkBook1()

Dim strName As Integer
strName = InputBox("Please Enter your number", "Test", , 1000, 1000)
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Workbooks (*.xlsx), *.xlsx")
If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If

End Sub

 

[avishain]

hey,

thank u for helping.


it works great except of that the filename of the copy isnt the number that i entered.


when the inputbox asks me "enter your number" ,i want that the same number will be used as the file name.

for instace, if i entered 1111,the copy of the file will be saved as 1111.xlsx


thank you

 

[shadow12345]

I have no idea why the other doesn't work... but i use this one to save the file as another name with an input box.

sub save_it()
Dim fname
fname = inputbox ("Enter a filename")
if fname <>"" then
fname = fname & ".xlsx"
ActiveWorkbook.SaveAs Filename:=fname
end if
end sub