In this article, you will learn how to copy the data from one workbook to different sheets.
If you have to allocate 10 k rows of data or more among all the team members; allocating the work to their respective sheets manually would be a bit tough ask. We can use VBA codes to help us.
Let us take an example:
We have data that contains Names, Product, Sales Person, Sales, etc.
To allocate the data among team members in column A, we need to manually apply filter & then copy the data & paste in their respective sheets.
Following is a snapshot of the data with list of Names in column H
To automate the manual steps, you are required to write VBA code.
Click on Developer tab
From Code group, select Visual Basic
Click on Insert, and then Module
This will create new module.
Enter the following code in the Module
Dim Counter As Integer
Dim SheetnameAs String
For Counter = 1 To 4
On Error Resume Next
Sheetname = Sheets(“data”).Range(“H” & Counter).Value
Range(“A1″).CurrentRegion.AutoFilter field:=1, Criteria1:=Sheetname
For better view, refer below snapshot of VB editor
This will copy the data from one sheet to other sheets.
In case the team members are increased or decreased then, you have to enter their names in column H & rest will be taken care by our code.
In this way, you can allocate the work to your team members using VBA codes & save your time.