mldaigle1
asked on
Excel Macro to copy specific Tabs into new workbook
Hi,
I need your help in order to copy 2 Tab into a new workbook and then prompt me to save as with a default name that i can modify just a llittle bit before i press save.
My Tabs' name are "Summary" and "Table"
My default file name is "HC - MMMM - YYYY"
I need your help in order to copy 2 Tab into a new workbook and then prompt me to save as with a default name that i can modify just a llittle bit before i press save.
My Tabs' name are "Summary" and "Table"
My default file name is "HC - MMMM - YYYY"
You should probably add this function
Function FileExist(FilePath As String) As Boolean
'Source: http://www.rondebruin
Dim TestStr As String
'Test File Path (ie "C:\Users\Chris\Desktop\Test\book1.xlsm")
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
'Determine if File exists
If TestStr = "" Then
FileExist = False
Else
FileExist = True
End If
End Function
and then modify Simon's code with something like thisSub CopyToNewWorkBookAndSave()
Sheets(Array("Summary", "tabs")).Copy 'Copy without any further arguments copies to a new workbook and makes it the activeworkbook
'Prompt for save-as name
saveName = Application.GetSaveAsFilename("HC " & Format(Now(), "mmmm") & "-" & Format(Now, "yyyy"))
If saveName <> False Then
If vbYes = MsgBox("The file already exists, do you want to overwrite it?", vbexclamtion + vbYesNo) Then
add modify code here to do what you want
'ActiveWorkbook.SaveAs saveName
End If
End If
End Sub
ASKER
Hi,
I did try both solution and they are working fine. Thanks,
Would it be possible to add the code to close the temporary workbook after I save the file without asking if i want to save it? That way, it will bring me back to the master file where i ran the macro?
I did try both solution and they are working fine. Thanks,
Would it be possible to add the code to close the temporary workbook after I save the file without asking if i want to save it? That way, it will bring me back to the master file where i ran the macro?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks alot, this is wonderful!!
Open in new window
Further details on the GetSaveAsFilename options here
Works on Mac & PC.