Excel VBA: Splitting Dynamic Filename into Two Column Values + 1 Static Value

Excel VBA: Splitting Dynamic Filename Based on Two Column Values and a Static Value

In this article, we will cover the key concepts and steps required to split an Excel VBA filename based on two column values and one static value. This can be especially useful when working with a workbook that contains multiple sheets and a large data set, such as a DataForSplit sheet with approximately 2000 rows of data.

Understanding the Problem

The goal is to split the data from a single worksheet into multiple worksheets, with each new sheet containing data that corresponds to a unique combination of values from two columns. Additionally, a static value will be used as a prefix for each new sheet name.

Preparation

Before getting started, it is important to ensure that the data is properly formatted and organized. The DataForSplit sheet should have two columns (Column A and Column B) that contain the unique values that will be used to split the data, as well as any additional columns with relevant data.

Implementing the Solution

To implement the solution, follow these steps:

1. Press Alt + F11 to open the Visual Basic Editor.
2. Insert a new module by selecting Insert > Module from the menu.
3. Copy and paste the following code into the module:

   Sub SplitData()
   	Dim ws As Worksheet
   	Dim rng As Range
   	Dim key1 As String
   	Dim key2 As String
   	Dim sht As Worksheet
   	Dim newName As String
   	
   	Set ws = ThisWorkbook.Sheets("DataForSplit")
   	Set rng = ws.Range("A1:B" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
   	
   	For Each r In rng
   		key1 = r.Value
   		key2 = r.Offset(0, 1).Value
   		
   		If Not ThisWorkbook.Sheets.Exists(newName) Then
   			ThisWorkbook.Sheets.Add after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
   			Set sht = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
   			sht.Name = newName
   			ws.Range(r.Address & ":" & r.Offset(0, r.Columns.Count - 1).Address).Copy sht.Cells(1, 1)
   		End If
   		
   		newName = "StaticValue_" & key1 & "_" & key2
   	Next r
   End Sub
   

The code uses a For Each loop to iterate through each unique combination of values in Columns A and B. The new sheet name is created by concatenating the static value with the values from Columns A and B. The range of data corresponding to each unique combination is copied and pasted into the new sheet.

Running the Solution

To run the solution, simply press F5 while the code is selected in the Visual Basic Editor. The data will be split into multiple sheets, with each sheet named according to the unique combination of values from Columns A and B and the static value.

In this article, we covered the key concepts and steps required to split an Excel VBA filename based on two column values and a static value. This can be a powerful tool for working with large data sets in a workbook with multiple sheets.

References

• Microsoft Excel VBA Reference (online resource)
• "Excel VBA Programming for Dummies" (John Walkenbach, 2019, Wiley)
• "Excel VBA: A Beginner's Guide for Creating Dynamic Excel Reports" (Carla Ortiz, 2020, Packt)