Storing Multiple Sheet References in a Cell to Use in a Macro

[Flinger]

I have a large workbook that I need to split into smaller parts, saved and emailed to people. I am creating a control sheet to simplify coding and support. The control sheet will have three columns.

Column 1 = the sheets to be sent
Column 2 = file name
Column 3 = email address

I have written a macro to loop through the control sheet. First it selects the sheets in Column 1, then it copies the selected sheets saves the new workbook with the name in Column 2, and emails the workbook to Column 3.

The problem I am having is how do I store the sheets to be sent in a single cell and how do I use it in my macro. This is a simplified version of what I have that doesn't contemplate the email portion yet.

Sub Email()
Dim strTabs As String
Dim strName As String
Dim strEmail As String

  
For Row = 1 To 5
    
 
strTabs = ActiveSheet.Cells(Row, 1)
strName = ActiveSheet.Cells(Row, 2)
strEmail = ActiveSheet.Cells(Row, 3)
    
    
Sheets(strTabs).Select
Sheets(strTabs).Copy
ActiveWorkbook.SaveAs Filename:= _
        "C:\Test\" & strName, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

  
Next Row

End Sub

My question boils down to how do I store the tabs names in Column 1 and how do I need to reference them in the code?

 

[gaj104]

Give this a go. It assumes you enter in the sheets you need seperated by a comma without a space.

i.e. in A1 you have - Main,Summary,Test1

Sub Email()
Dim strTabs As String
Dim strName As String
Dim strEmail As String
Dim Row As Long

Dim ws As Worksheet
Dim wb As Workbook
  
Set ws = ActiveSheet
  
For Row = 1 To 5
    
 
    strTabs = ws.Cells(Row, 1)
    strName = ws.Cells(Row, 2)
    strEmail = ws.Cells(Row, 3)
    
    'Create new workbook to save sheets into
    Set wb = Workbooks.Add
    
    'Copy sheets into new workbook
    ThisWorkbook.Sheets(Split(strTabs, ",")).Copy wb.Sheets(1)
    
    wb.SaveCopyAs Filename:= _
        "C:\Test\" & strName ', _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        
    wb.Close False

  
Next Row

End Sub