Vba to Copy ActiveWorksheet, then open SaveAs Dialog Box to pick Subfolder

MikeL

Active Member
Joined
Mar 17, 2002
Messages
488
Office Version
  1. 365
Platform
  1. Windows
Hello,
I would like to copy the active worksheet, name as the current sheet name, then have the dialog box for save as open with a choice of subfolders under C:\test\

here is the start of my code

Dim FileName As String
Dim FolderName As String

ActiveSheet.Copy
FileName = ActiveSheet.Name
FolderName = BrowseFolder(Caption:="Select A Folder", InitialFolder:="C:\test")

How to complete this to saveas an .xlsm format under the subfolder selected?

Thanks
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi Mike,,, try this

Code:
Sub ActiveCopy()
Dim FileName As String
Dim FolderName As String
Dim fPth As Object
Dim fPath As String
ActiveSheet.Copy
FileName = ActiveSheet.Name
Set fPth = Application.FileDialog(msoFileDialogFolderPicker)
        
        With fPth
            .InitialFileName = "C:\test\"
            .Title = "Select Data File :"
            .InitialView = msoFileDialogViewList
            
        End With
        
        
        If fPth.Show = True Then
            fPath = fPth.SelectedItems(1)
            ActiveWorkbook.SaveAs FileName:=fPath & "\" & FileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,150
Messages
6,106,257
Members
448,010
Latest member
wbarkwell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top