December 4, 2021
I had a working macro, and I am attempting to upgrade it, and that is where I am having issues. This macro copies the visible rows that have been auto-filtered. The range is A-V (although some columns are deleted in the macro). Now the copying and original saving of this file work perfectly too. I added a browse for folder location (which also works). However, the one part that does NOT work is the population of the file name based on the cell references. These cell references consist of a combination of data entry cells (input by the user) and formula cells. The button to run the macro is on the Instructions sheet, where these cell variables are. And the sheet that is being copied is the sheet named "Impact".
' Optimize Macro Speed
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim newBook As Excel.Workbook
Dim rng As Excel.Range
Dim fname As String ' Output File Name
Set newBook = Workbooks.Add
Set rng = ThisWorkbook.Worksheets("Impact").Cells.SpecialCells(xlCellTypeVisible)
' Auto data
' Convert Range to Table
Set rng = Range("A1").CurrentRegion 'Change to match your range
ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "tbl_data"
' Change Font to Black
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
' Delete Unneeded Columns
' InScope, Method, Helper, Method Helper
fname = Range("U20").Value & " - " & Range("U22").Value & " - " & Range("U23").NumberFormat = "mm-dd-yyyy" & ".xlsx"
' Starting Cell
' activates cell
' Reset Macro Optimization Settings
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
November 1, 2018
This part of your code to set the file name:
Range("U23").NumberFormat = "mm-dd-yyyy"
will return True/False depending on the numberformat applied to the cell. It will not format the cell content as a date. What I suspect you wanted is:
fname = Range("U20").Value & " - " & Range("U22").Value & " - " & Format$(Range("U23").Value, "mm-dd-yyyy") & ".xlsx"
December 4, 2021
That did not work. It display a blank in the file name. And apparently the code I chose to BROWSE is designed for file opening, not saving, as an "open" button appeared, see screenshot. An other help you can offer? Perhaps instead of using a browser to navigate the folder location, the default should be where ever the xlsb file is that the copy is being created from. It can be saved in the same location, or the user can navigate from there. The code I am using for the browse folder is as follows:
On Error GoTo err
Dim fileExplorer As FileDialog
Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
'To allow or disable to multi select
fileExplorer.AllowMultiSelect = False
If .Show = -1 Then 'Any file is selected
[FilePath] = .SelectedItems.Item(1)
Else ' else dialog is cancelled
MsgBox "You have cancelled the dialogue"
[FilePath] = "" ' when cancelled set blank as file path.
November 1, 2018
That particular dialog is for choosing a file, not a folder, and you didn't specify the InitialFileName property for the dialog anywhere.
It would seem to make more sense to me to use Application.GetSaveAsFileName for this though? Also, it's generally better to specify a particular sheet (even if it's just activesheet) when referring to ranges in the code.