Hi,
please, can you help me?
Can you edit this macro for me so that the new file has this naming rule:
"always from first sheet AB2"_"always from first sheet U2"_"always from first sheet V2"_"sheet name of currently open sheet"
Thanks
please, can you help me?
Can you edit this macro for me so that the new file has this naming rule:
"always from first sheet AB2"_"always from first sheet U2"_"always from first sheet V2"_"sheet name of currently open sheet"
Thanks
Code:
Sub all_selected_to_new2()
Dim mySourceWB As Workbook
Dim mySourceSheet As Worksheet
Dim myNewFileName As String
Dim FileName As String
' First capture current workbook and worksheet
Set mySourceWB = ActiveWorkbook
Set mySourceSheet = ActiveSheet
' Build new file name based
Filename = ActiveWorkbook.Name
If InStr(Filename, ".") > 0 Then
Filename = Left(Filename, InStr(Filename, ".") - 1)
End If
' probably the filename shall be prepared different way, not just after one (active) sheet name
myNewFileName = mySourceWB.Path & "\" & Filename & "_" & mySourceSheet.Name & ".xlsx"
' Don't just add new workbook but copy selected sheets to a new workbook (it will become active one)
Activewindow.SelectedSheets.Copy
'and save with name of sheet from other file (overwriting without warning if workook with such name already exists )
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myNewFileName
Application.DisplayAlerts = True
'as you use workbook and worksheet objects it would be elegant (or in some situations also wise) to clear them all, so:
Set mySourceSheet = Nothing
Set mySourceWB = Nothing
End Sub