VBA Code - Set up Button (Form Control)

[gary2014]

Hi,
I have created an excel sheet in which I have set 4 Buttons (Form Control).
1. Reset
2. Korfball
3. Sepaktakraw
4. Kabbadi

Now I want to add another button "Save".
Once the user clicks on the "Save" button, only that particular sheet should go and save at a desired location "G- Drive".


12852

[Kenneth Hobs]

I am surprised that you have not received a response yet. I looked at this a few times and had some questions so rather than ask, I made some assumptions.

Create your form button and set it to run this Sub from a Module. Change the tFolder and tFile values to suit. Set the FSO reference as I commented.


Rem Needs Tools > References... > MicroSoft Scripting Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Sub fsoCopyFile()
Dim fso As New FileSystemObject
Dim tFile As String, tFolder As String, s As String
Dim wb As Workbook, ws As Worksheet

'Target folder name.
tFolder = ThisWorkbook.Path & "\TargetFolder\" 'Must have trailing \.
'Target filename.
tFile = "aPrefix_" & ThisWorkbook.Name 'Excel does not allow two files with same name open.

'Current sheet name
s = ActiveSheet.Name

With fso
'Exit if target folder does not exist.
'If Not .FolderExists(tFolder) Then
' MsgBox pFolder & " does not exist.", vbCritical, "Macro Ending"
' Exit Sub
'End If

'Create target folder if needed.
If Not .FolderExists(tFolder) Then .CreateFolder tFolder


'Copy current file to target folder with name of current file.
.CopyFile ThisWorkbook.FullName, tFolder & tFile, True 'True overwrite.
End With

'Open target file workbook, delete all sheets except current, save, and close
Set wb = Workbooks.Open(tFolder & tFile)
With wb
For Each ws In .Worksheets
If ws.Name <> s Then ws.Delete
Next ws
.Close True
End With
End Sub

[gary2014]

13159

Sir, I am not able to bring the above code in working.

In the Image inserted above, I have created 4 buttons, now I want to add another button "Save as" which should appear like Reset button as it looks like in the image.

Please help me in getting this fixed.

[Yongle]

Are we not solving virtually the same problem twice as on this thread (http://www.vbaexpress.com/forum/showthread.php?52280-Save-As-button-using-VBA-Code&p=323031&highlight=#post323031) or am I missing something ?