Edit macro

[KreshBell]

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

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

 

[Marc L]

Hi, attach a workbook at least and give an example of the full filename you expect for …​

​

 

[KreshBell]

Hi, sorry for delay
Thanks for your ansver. Please find attached sample files. Source file and final file

 

[Marc L]

According to the previous post a VBA demonstration for starters :​

Sub Demo1()
    F$ = ActiveWorkbook.Path & ActiveWorkbook.Sheets(1).["\"&AB2&"_"&U2&"_"&V2&"_"] & ActiveSheet.Name
    ActiveWindow.SelectedSheets.Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs F, 51
    Application.DisplayAlerts = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[KreshBell]

Hi, sorry for delay, I was on trip. This is perfect!! Thanks so much