Transfer Excel data from one sheet to another: VBA

Transfer Excel data from one sheet to another: VBA

In this article, we will provide a VBA code to transfer data from one Excel sheet to multiple others while respecting specific conditions.

excel-sheet-to-another">To transfer data from one Excel sheet to another:

You can use following codes

Sub SplitSheets() 
Dim DataSht, wsCrit, SplitSht As Worksheet 
Dim lrUnq, lrData, i As Long 
Dim FtrVal As String 
Application.ScreenUpdating = False 
Set DataSht = Worksheets("sheet1") 'change it to the name of your raw data sheet 
lrData = DataSht.Range("a" & Rows.Count).End(xlUp).Row 
Set wsCrit = Worksheets.Add 
DataSht.Range("B1:l" & lrData).AdvancedFilter Action:=xlFilterCopy, _ 
CopyToRange:=wsCrit.Range("A1"), Unique:=True 
lrUnq = wsCrit.Range("a" & Rows.Count).End(xlUp).Row 
For i = 2 To lrUnq 
FtrVal = wsCrit.Range("A" & i).Value 
Set SplitSht = Worksheets.Add 
DataSht.Select 
'DataSht.ShowAllData 
ActiveSheet.AutoFilterMode = False 
ActiveSheet.Range("A1:Z" & lrData).AutoFilter Field:=2, Criteria1:=FtrVal 
Range("a1").Select 
Range(Selection, Selection.End(xlToRight)).Select 
Range(Selection, Selection.End(xlDown)).Select 
Selection.Copy 
SplitSht.Select 
Range("A1").Select 
ActiveSheet.Paste 
'Cells.Select 
Cells.EntireColumn.AutoFit 
SplitSht.Name = FtrVal 
Application.CutCopyMode = False 
Next i 
Application.DisplayAlerts = False 
wsCrit.Delete 
Application.DisplayAlerts = True 
.AutoFilterMode = False 
End Sub
Any more excel questions? check out our forum!
Subject Replies
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11
Removing " false" value in "IF" 10
Automatically transfer data from one sheet to another 22
Comparing two columns of names in Excel 8
Excel - To generate unique ID 4
converting number into words (Excel) 6

Excel