Use of Macro to Un-merge / Split data from single excel sheet into Multiple excel sheet using VBA
Use of Macro to Un-merge single excel sheet into Multiple excel sheet using VBA
Based on a column and its values, this Macro creates multiple file and save the file in the system
Step 1 : - Copy and paste below given code in your excel file by pressing Alt + F11 and save file in anywhere in your computer with file name unmerge.xlsm
Step 2 : - Once you saved the file, choose any data for testing the Macro and paste it in sheet1 and please ensure that the data heading should be pasted in 1st row
Step 3:- Click on View menu and then click Macros, then View Macros and then run macro name Unmerge
Different file will be created based on the inputs entered by you.
******************************************************************
Sub unmerge()
Dim i As Long
Dim unmerge As Variant, arrbooks() As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheet1.AutoFilterMode = False
Dim column As String
Dim field As Integer
Dim rng As range
field = Application.InputBox("Select the column no. on which you want to apply filter and create multiple sheets, then input the column in number, let's say column 'C' as 3, column 'E as 5:", "Values", Type:=2)
'
column = Application.InputBox("Identify the column using which you want to create multiple file and then List down the value seperated by commas:", "Values", Type:=2)
'Set rng = Application.InputBox("Type the data range on which you want to apply filter and create multiple sheets:", Type:=8)
MsgBox ("Please ensure that the heading should be in the first row")
unmerge = Split(column, ",")
ReDim arrbooks(0 To UBound(unmerge))
For i = 0 To UBound(unmerge)
Set arrbooks(i) = Workbooks.Add
Next
With Sheet1
For i = 0 To UBound(unmerge)
.range("a1:aa500000").AutoFilter field:=field, Criteria1:=unmerge(i)
.range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
Workbooks(arrbooks(i).Name).Sheets(1).range("a1").PasteSpecial
Next
End With
For i = 0 To UBound(arrbooks)
Workbooks(arrbooks(i).Name).SaveAs Filename:=ThisWorkbook.Path & "\" & unmerge(i) & ".xlsx"
Next
Application.ScreenUpdating = False
Sheet1.AutoFilterMode = False
Application.DisplayAlerts = False
End Sub
********************************************************
Links to my videos
MID Function - Extract number / words from a dynamic position / position not fixed
• MID Function - Extract number / words from...
Change date format using Text Function
• Change date format using Text Function
How to protect rows or columns - Allow edit range allows you to edit only selected ranges in Excel
• How to protect rows or columns - Allow ed...
Concatenate function used with text function, joins two or more text / numbers into one
• Concatenate function used with text functi...
Text Function - change the way a number /text / date appears
• Text Function - change the way a number /t...
How to use Subtotal function to count visible cells only in a given range
• How to use Subtotal function to count visi...
Use of Subtotal function to sum / total of all visible cell in a given range
• Use of Subtotal function to sum / total of...
Use of Dollar Sign/ How to use $ Sign, Freeze rows or column and both
• Use of Dollar Sign/ How to use $ Sign, Fre...
How to console / Merge multiple text files into one text file using command prompt / DOS / CMD
• How to console / Merge multiple text files...
How to use SUMIFS function, sums a selected range with multiple criteria's / conditions
• How to use SUMIFS function, sums a selecte...
How to create aging / Bucketing using IF Function in excel
• How to create aging / Bucketing using IF F...
SUMIF Function - Sumif function, sums up a range with a single condition / Criteria
• How to use SUMIFS function, sums a selecte...
How to create aging / Bucketing using IF Function in excel
• How to create aging / Bucketing using IF F...
SUMIF Function - Sumif function, sums up a range with a single condition / Criteria
• SUMIF Function - Sumif function, sums up a...
Use of Countif Function - How to count cells in a range with a condition / Criteria
• Use of Countif Function - How to count ce...
Use of CountBlank Function - Count empty cells in a given range in Excel
• Use of CountBlank Function - Count empty c...
Use of CountA Function- CountA function use and comparison/Difference of CountA with Count function
• Use of CountA Function- CountA function us...
Count Function - How to count numeric value in MS Excel using Count Function
• Count Function - How to count numeric valu...