ActiveWorkbook.SaveAs with vlookup and cell.Value

[TBP]

Hi im using Split an Excel File in to Many using VBA from Chandoo.org

In the SaveAs part how would I change "& cell.Value &" to something like "& vlookup(cell.Value,MyNamedRange,2,0) &"

Dim cell As Range
Dim curPath As String
    
curPath = ActiveWorkbook.Path & "\"
    
For Each cell In Range("lstSalesman")
     [valSalesman] = cell.Value
     Range("myList").AdvancedFilter Action:=xlFilterCopy, _
         criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
     Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
     Workbooks.Add
     ActiveSheet.Paste
     ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
     ActiveWindow.Close
     Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents
Next cell

 

[jumbledore]

you can just replace cell.value with a variable say "TheFile" and assign a value like so:

TheFile=Application.Vlookup(cell.Value,MyNamedRange,2,0)

 

[TBP]

you can just replace cell.value with a variable say "TheFile" and assign a value like so:

TheFile=Application.Vlookup(cell.Value,MyNamedRange,2,0)

Im not exactly sure how how to do what you have suggested?
I get a "Compile error: Variable not defined" with my below attempt.

Dim cell As Range
Dim curPath As String
    
curPath = ActiveWorkbook.Path & "\"

For Each cell In Range("lstSalesman")
   [valSalesman] = cell.Value
   Range("myList").AdvancedFilter Action:=xlFilterCopy, _
       criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
   Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
   Workbooks.Add
   ActiveSheet.Paste
        
   TheFile = Application.VLookup(cell.Value, MyNamedRange, 2, 0)
        
   ActiveWorkbook.SaveAs Filename:=curPath & TheFile & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
       FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
   ActiveWindow.Close
   Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents
Next cell

 

[jumbledore]

where do you get this error?

 

[TBP]

TheFile = Application.VLookup(cell.Value, MyNamedRange, 2, 0)

MyNamedRange is highlighted

 

[jumbledore]

TheFile = Application.VLookup(cell.Value, MyNamedRange, 2, 0)

MyNamedRange is highlighted

MyNamedRange is soemthig u have to define which is nothing but the range you wish to split

 

[TBP]

MyNamedRange is soemthig u have to define which is nothing but the range you wish to split

Like adding this

Dim MyNamedRange As Range

Error highlighting is now TheFile

 

[TBP]

MyNamedRange is soemthig u have to define which is nothing but the range you wish to split

Hi jumbledore im not sure what you mean and how to get working?
Here is a link to my example file on OneDrive

Dim cell As Range
Dim curPath As String
    
'I added this
Dim MyNamedRange As Range
    
curPath = ActiveWorkbook.Path & "\"

    
For Each cell In Range("lstSalesman")
    [valSalesman] = cell.Value
    Range("myList").AdvancedFilter Action:=xlFilterCopy, _
        criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
    Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
    Workbooks.Add
    ActiveSheet.Paste
        
    'I added this
    TheFile = Application.VLookup(cell.Value, MyNamedRange, 2, 0)
        
    'I changed cell.Value > TheFile
    ActiveWorkbook.SaveAs Filename:=curPath & TheFile & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents
Next cell

 

[TBP]

Worked out an answer with some more help here ActiveWorkbook.SaveAs with vlookup and cell.Value Ozgrid Forums