VBA - Save Specific Sheets as New Workbooks with File name corresponding to C5 from each sheet.

[AndrewDrewAndy]

I have a workbook with 10 active files, but I only need to save sheets ("Export 1", "Export 2", "Export 3", "Export 4", "Export 5"). I have a file path I want to save them in, but I cannot find a code to save each sheets filename to the .txt in each sheets' C5 cell (each sheet has a different person's name that the file needs to be saved as). It doesn't work to change the worksheet names because the file is active and changes ofte

 

[AndrewDrewAndy]

How about

Sub AndrewDrew()
    Dim Ary As Variant
    Dim i As Long
    Dim Pth As String
   
    Pth = Sheets("Master Data").Range("A1")
    If Not Right(Pth, 1) = "\" Then Pth = Pth & "\"
    Ary = Array("Export 1", "Export 2", "Export 4")
    For i = 0 To UBound(Ary)
        Sheets(Ary(i)).Copy
        With ActiveWorkbook
            .SaveAs Pth & Range("C5").Value, 51
            .Close False
        End With
    Next i
End Sub

This is great. I am just learning that file paths for desktop and mac are much different. Is there a way to build in for the vba to save to either Mac or Desktop depending on which is running the excel.

Again, I am very appreciative of your help.

 

[Fluff]

Yes that's possible, but I know nothing about Macs, or their path structure, so cannot help further.