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
Excel
- Transfer specific data from one worksheet to another for reports
- How to transfer data from one sheet to another in excel using macros
- Excel vba transfer data from one sheet to another
- Copy data from one excel sheet to another: automatically > Guide
- Moving specific data into separate worksheets [solved] > Forum - Excel
- Transferring Selected Data from Multiple Worksheets to One [solved] > Forum - Excel
- Copy specific data from Master sheet to multiple sheets. > Forum - Excel
- Transfer WhatsApp messages: to new phone, android to iPhone > Guide
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Fix arrows keys in Excel: Scroll lock, alternative method
- How to take screenshot in Excel: shortcut, sheet, cell
- How to apply a function to multiple sheets on Excel
- Convert numbers to words in Excel: without VBA, formula
- How to change author name in Excel: spreadsheet, VBA
- How many IF statements can you nest in Excel
- How to use Excel color cell if formula
- How to display multiple columns in a validation list
- How to use an IF statement to add 1 to total
- How to perform a partial cell match in Excel?
- How to run macro when data entered in a cell
- Check if a value exists in an array VBA
- Search and find using VBA in Excel
- VBA select case like: operator, string, statement
- What is the VBA code to select last sheet in workbook
- VBA Excel color codes: index number, list
- How to insert file path in excel: cell, sheet
- How to disable auto recover in Excel
- How to enable VBA in Excel: Mac, Office 365
- How combine IF function, SEARCH and ISERROR in Excel
- How to enter multiple lines in a single Excel cell
- How to insert GIF in Excel: 365, sheet, VBA
- How to count names in Excel: formula, using COUNTIF
- Open and convert an Excel file in Notepad
- Repeat rows in Excel: based on cell value, VBA
- How to automatically transfer data between sheets in Excel
- How to manipulate data in Excel: VBA
- How to copy a Macro into a blank cell
- Insert a hyperlink in Excel: with text, to another tab
- Recover Excel file: previous version
- Run macro on opening: worksheet, workbook
- How to transfer data from one Excel sheet to another?
- Most useful Excel formulas: for data analysis
- Recalculate Excel Workbook Before Saving
- How to apply if function in Excel: with dates, with text
- Select the default number of processors in Excel
- Insert picture in Excel: cell, shortcut, using formula
- How to add sheet to workbook: VBA, Excel
- Unlock password protected Excel file: Macro
- Excel functions in French
- How to create a cascading combo box: Excel, VBA
- How to create calculator in Excel VBA
- Excel export data: from one sheet to another
- How to add a number of days to a date in Excel
- How to fill multiple Excel sheets from master sheet
- How to copy data to another workbook: using VBA
- Excel VBA add command button programatically
- How to change a column to numeric in Excel?
- Mark sheet grade formula in Excel: template
- How to connect VB 6.0 with MS Access
- How to remove leading apostrophe in Excel
- How to create UserForm: in Excel, VBA
- Select empty cell in Excel: VBA
- Excel send value to another cell
- Using VBA to find last non empty row: in column, in table
- How to show or hide formula bar in Excel: VBA, shortcut
- How to change enter key function in Excel
- How to use Excel auto numbering formula
- Conditional formatting with dates: in Excel
- How to calculate VAT in Excel: formula
- Transfer a worksheet to another Excel workbook: without VBA
- Delete duplicates in Excel: column, formula
- Send email with attachment Excel: VBA, macros
- How to Insert a Transparent Image into an Excel Document
- Compare two Excel sheets: and combine data
- Copy data from one Excel workbook to another
- Auto generate serial number in Excel: VBA, formula
- How to generate email notifications for Excel updates
- How to create a drop-down List in Excel
- Split a workbook into individual files in Excel
- How to insert blank rows using macros in Excel
- Run Macros in Excel: online, shortcuts, VBA
- How to clear formatting in Excel
- Insert an image into an Excel comment box
- Credit summation formula
- Create new sheet based on cell value: Excel, macro
- How to insert multiple rows in Excel with a macro?
- How to change Excel date format
- How to create a timer in Excel VBA
- How to use conditional formatting in Excel
- How to count occurrences of characters and numbers in Excel
- Shortcuts to insert in Excel
- How to copy data to multiple worksheets in Excel
- Excel IF, AND, OR, and NOT functions
- Create a new workbook and copy data in Excel via a Macro
- Update dates in Excel spreadsheet: each year, formula
- How to change row color in Excel VBA based on value