VBA loop to export specific sheets from workbook

[p4nny]

Hi

I would like a macro that would select specific sheets based off the tab name using a list on a separate tab, move them to a new workbook and save in a location that i would specify (Locally).

Basically, in a one excel sheet I have list of salesman's names (tab is called ‘TM list’ and a corresponding tab in the same workbook matching those names.

What I would like is the macro to select a name of Salesman from the list, move it to new workbook along with a tab called raw data (and hide in the newly created workbook and also the in the active select cell B13 before saving.
I suppose the rule would be along lines of ... loop until column A in salesman list is blank.
Ideally it would save the name of the tab plus todays date would be great.

Appreciate your help, Hope this is clear.

Anthony

 

[Norie]

Perhaps something like this.

Dim wbNew As Workbook
Dim rngTM As Range
Dim strPath As String

    Application.ScreenUpdating = False

    strPath = "C:\PathToFolder\You\Want\ToSaveTo\"
    Set rngTM = Sheets("TM List").Range("A2")

    Do
        Sheets(Array("Raw Data", rngTM.Value)).Copy
        Set wbNew = ActiveWorkbook
        With wbNew
            .Sheets("Raw Data").Visible = False
            Application.Goto .Sheets(1).Range("B13"), True
            SaveAs strPath & rngTM.Value & Format(Date, "ddmmmyyyy") & ".xlsx, xlOpenXMLWorkbook
            .Close
         End With
         Set rngTM = rngTM.Offset(1)
    Loop Until rngTM.Value   

    Application.ScreenUpdating = True

 

[p4nny]

this works great.. Just one question, what is "sheets(1)"? Many thanks for your help