I have a range of values from A2 to B6 and I wish to save those cells in a new workbook. The destination folder is in (C:) and has the name of "Products" so the new workbook must be inside this folder.

Also, as a Plus, It would be amazing if before saving the file, the macro could ask me through an InputBox what file name I want to give the new excel file.

CodePudding user response：

Something like this would do the work:

Sub CopyToNewWb()

Dim wb As Workbook
Dim wb_New As Workbook

Set wb = ThisWorkbook
Dim wbstring As String
Dim input_file_name As String

input_file_name = InputBox("Enter file name", "Enter new workbook file name") 'Create input box

wbstring = "C:\Products\" 'Set workbook path
Workbooks.Add.SaveAs Filename:=wbstring & input_file_name & ".xls", FileFormat:=56 'I would add date here if user enters the same name twice...

Set wb_New = ActiveWorkbook

wb_New.Worksheets("Sheet1").Range("A2:B6").Value = wb.Worksheets("Sheet1").Range("A2:B6").Value 'Copy Range from wb to new workbook

End Sub

For different file format numbers, please see the link: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat