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.
 
img1
 
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
 
img2
 
To automate the manual steps, you are required to write VBA code.
 
Click on Developer tab

From Code group, select Visual Basic
 
img3
 
Click on Insert, and then Module
 
img4
 

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

 

Err.Clear

Range(“A1″).CurrentRegion.AutoFilter

End Sub
 
img5
 
For better view, refer below snapshot of VB editor
 
img6
 
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>