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.
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
| Name | Value | Description | Extension |
|---|---|---|---|
| xlCSV | 6 | CSV | *.csv |
| xlExcel8 | 56 | Excel 97-2003 Workbook | *.xls |
| xlExcel9795 | 43 | Excel version 95 and 97 | *.xls |