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".
Option Explicit
Sub CopyVisable()
' 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
rng.Copy newBook.Worksheets("Sheet1").Range("A1")
Cells.Select
Cells.EntireColumn.AutoFit
' 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
Range("K:S").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
' Sort
Call MySort
' Delete Unneeded Columns
' InScope, Method, Helper, Method Helper
Range("S:V").Delete
fname = Range("U20").Value & " - " & Range("U22").Value & " - " & Range("U23").NumberFormat = "mm-dd-yyyy" & ".xlsx"
Call browseFilePath
' Starting Cell
' activates cell
Range("A1").Select
' Reset Macro Optimization Settings
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
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"
@Velouria,
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:
Sub browseFilePath()
On Error GoTo err
Dim fileExplorer As FileDialog
Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
'To allow or disable to multi select
fileExplorer.AllowMultiSelect = False
With fileExplorer
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.
End If
End With
err:
Exit Sub
End Sub
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.
@Velouria,
Yes, that was exactly what I was attempting to do. I got that confused. Thanks so much for clarifying my error. It works great now.
