VBA - creating new worksheets with a variable name

[Autumnscribe]

Hi All

Complete VBA noobie. Other modules are working now need to fix the export module.

Need to split up files based on column B - source system. It has the values AX or COM. Code does the splitting great - creates 2 new workbooks but doesn't name them.

Where I've added the comment (in CAPITALS) inside the IF statement, the variable strColumnValue is AX then COM. Does anyone know how at that point I can create and name the 2 workbooks?

Sub SplitSheetDataIntoMultipleWorkbooksBasedOnSpecificColumn()
    Dim objWorksheet As Excel.Worksheet
    Dim nLastRow, nRow, nNextRow As Integer
    Dim strColumnValue As String
    Dim objDictionary As Object
    Dim varColumnValues As Variant
    Dim varColumnValue As Variant
    Dim objExcelWorkbook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
 
 'Set CombinedData as the activeworksheet
 Worksheets("CombinedData").Activate
 
    Set objWorksheet = ActiveSheet
    nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
 
    Set objDictionary = CreateObject("Scripting.Dictionary")
 
    For nRow = 2 To nLastRow
        'Get the specific Column
        'Here source (system) is in column B
        'The column can be changed here
        strColumnValue = objWorksheet.Range("B" & nRow).Value
 
        If objDictionary.Exists(strColumnValue) = False Then
           objDictionary.Add strColumnValue, 1
           MsgBox strColumnValue ' HERE THE  VARIABLE VALUES ARE AX THEN COM - How do I create workbooks with those names at this point?
        End If
    Next
 
    varColumnValues = objDictionary.Keys
 
    For i = LBound(varColumnValues) To UBound(varColumnValues)
        varColumnValue = varColumnValues(i)
 
        'Create a new Excel workbook
        Set objExcelWorkbook = Excel.Application.Workbooks.Add
        Set objSheet = objExcelWorkbook.Sheets(1)
 
        objWorksheet.Rows(1).EntireRow.Copy
        objSheet.Activate
        objSheet.Range("A1").Select
        objSheet.Paste
 
        For nRow = 2 To nLastRow
            If CStr(objWorksheet.Range("B" & nRow).Value) = CStr(varColumnValue) Then
               'Copy data with the same column "B" value to new workbook
               objWorksheet.Rows(nRow).EntireRow.Copy
 
               nNextRow = objSheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
               objSheet.Range("A" & nNextRow).Select
               objSheet.Paste
               objSheet.Columns("A:B").AutoFit

            End If
           
        Next
       
    Next
End Sub

MacroControlFile.xlsm
	A	B	C	D	E	F
1	ID	Source System	Product	Buy_Price	RRP	Status
2	ZN6213	AX	Mitre Box	35.2	48.5	Recommend approval
3	AB1234	AX	Skillsaw	48.5	71	Recommend approval
4	AP7201	COM	Hammer	7.75	11.3	Recommend approval
5	CD6543	COM	Screwdriver	3.25	5.75	Recommend approval
CombinedData

 

[EFANYoutube]

This may help you?

Dim wb As Workbook
    For nRow = 2 To nLastRow
        'Get the specific Column
        'Here source (system) is in column B
        'The column can be changed here
        strColumnValue = objWorksheet.Range("B" & nRow).Value
 
        If objDictionary.Exists(strColumnValue) = False Then
           objDictionary.Add strColumnValue, 1
           MsgBox strColumnValue ' HERE THE  VARIABLE VALUES ARE AX THEN COM - How do I create workbooks with those names at this point?
           Set wb = Workbooks.Add
           wb.SaveAs ThisWorkbook.Path & "\" & strColumnValue
           
        End If
    Next

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Create new workbooks with a variable name
and Create new workbooks with a variable name - OzGrid Free Excel/VBA Help Forum
not to mention Create new workbooks with a variable name
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.