How to Copy The Data From One Workbook To Different Sheets In Microsoft Excel 2010

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

Sub CopyData2Sheets()

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

Range(“A1″).CurrentRegion.Cells.SpecialCells(xlCellTypeConstants).Copy Destination:=Sheets(Sheetname).Range(“A1″)

Next Counter




End Sub
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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube