VBA Move data from one sheet to multiple sheets

Posted on May 14th, 2012 in Automation , Excel Howtos , Learn Excel , VBA Macros - 42 comments

This is a guest post by Vijay, our in-house VBA Expert.

Suresh sent an email with interesting problem.

There is one data entry sheet where all the data needs will be entered, however once done we want the data to be stored separately in multiple sheets designated by the Employee code.

In this article we will learn how to use VBA to help in resolving the problem Suresh was facing at work.

We will do this using VBA and the below procedure illustrates how this was done.

Sub copyPasteData()
Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim lastRow As Long

strSourceSheet = "Data entry"

Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select

Range("C2").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastRow = LastRowInOneColumn("A")
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Let’s understand the code

We start by defining 2 variables which will hold the Source and destinations sheet names, source will be one sheet only however there will be multiple destinations sheets.

A simple Do While loop is utilized to complete this data transfer from one sheet to the others.

However the most interesting line in the code is :

ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select

Since we start our work on Column C in the loop, we need to ensure the data from Column A and B is also selected to be copied and pasted on the relevant sheets, this is accomplished using the above line.

Once we have selected the entire data set on the current row, we copy the same and select the relevant sheet and then paste the data as values on the same.

At the end of the code once we switch back to the Source sheet, we need to ensure that we select Column C again. The reason for that is, when we select the entire data set we activate the first column and we need to come back to column C for our loop and the rest of the code to work.

 

Download Excel File

Click here to download the file & save it on your system and use it to understand this technique.

More on VBA & Macros

If you want to learn more about using VBA to automate reporting & email tasks, read these:

Join our VBA Classes

If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.

Click here to learn more about VBA Classes & join us.

Your email address is safe with us. Our policies

Written by Vijay Sharma
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

42 Responses to “VBA Move data from one sheet to multiple sheets”

  1. Gary Skelton says:

    Hi there,

    An excellent tip, as always.

    Interestingly, this is one of the features of ASAP Utilities for Excel.

    A google will soon find it.

    I wonder if you have ever tried this out?  It’s an amazing suite of Excel Time-Savers.

    I assure you that I am in no way affiliated with the authors – just wanted you to know that it was out there…

    Regards and thanks for a great website and emails!

    Gary Skelton     
      

  2. Suresh says:

    Dear Chandoo !

    after entry we change in data entry sheet its effect not representative in similar sheet,

    In this worksheet 
    when we entry in cell C2 employee code 1 its data transfer in sheet 1

    entry in cell C3 by employee code 2 then its data transfer in sheet 2 

    Problem is there after enter value in C3 then sheet 1′s row should not increase ,

     

    • r.karthik says:

      Dear sir,

      i am karthikeyan,r.karthikeyan78@yahoo.com i request u to please clarify my doubt in excel sheet move data from one to many sheet.

      if i put one entry in data entry sheet one name and various dates also various names in same date it saves and and goes to sheet1 if i run macro
      several times per day it shows multiple duplicate entries. It automatically
      creates like new entry when i running macro.

      I need solution for that please send your valuable suggestions.

      Here i attach my problem 

       

      s.r no
      Date
      employee code
      name
      Debit
      Credit
      Balance

      1
      5/10/2012
      muthu
      A
      100
      50
      50

      1
      5/10/2012
      muthu
      A
      100
      50
      50

      1
      5/10/2012
      muthu
      A
      100
      50
      50

       
       
       
       
       
       
       

       

  3. Andrew says:

    Dont download this guys! Like a mug i did it, but its not free, once its installed on your Excel ribbon they want an upgrade fee!
    I now cant get rid of it so everytime i open an eExcel file it goes looking for this darn programme which ive deleted!
    anyone know how to stop Excel looking for this programme?
    Thanks 

    • Gary Skelton says:

      Hi all,

      @Andrew – My apologies – I use this at a non-profit organisation, therefore I can use the free version – therefore I never had this problem.  Have you tried a) uninstalling it or b) unchecking the Add-in in options?

      @Suresh – Not quite following you, but the VBA is designed to copy your data and pasting it to another sheet, without any links to the original data.  This is a one-off process for splitting your data into multiple sheets by change of a value, consolidating data onto extra sheets and enabling you to easily report (or pring) a distinct set of values.

      Regards

      Gary

    • Hello Andrew,
      I’m sorry to read this. Next time, please contact us so we can help.
      Usually uninstalling ASAP Utilities will completely remove it.
      Do you get a message similar to “C:\Program Files\ASAP Utilities\ASAP Utilities.xla” could not be found.” when you start Excel?
      You can get rid of thIS message by following the following steps:
      http://www.asap-utilities.com/asap-utilities-uninstall-remove.php#temporary_disable

      More information on this error:
      http://www.asap-utilities.com/faq-questions-answers-detail.php?m=126
       
      I’m also sorry to read you feel mislead. We tried our best on the download page to show you when it is free:
      http://www.asap-utilities.com/download-asap-utilities.php
      The ASAP Utilities ‘Home and student’ edition is free and is (only) for non commercial use, such as at home, for students or for non-profit organizations such as Amnesty International. Commercial organizations however should buy a license. (They can try it for 90 days). The reason behind this is that if your organization makes a profit ASAP Utilities will help you reduce your costs and we ask to buy a license in return. The time saved with ASAP Utilities will recover the license costs within a few weeks.
      By offering a free edition of ASAP Utilities we want to contribute to society.
       
      Regarding this excellent article from Chandoo, below is the tool that Gary referred to:
      ASAP Utilities » Sheets » Split the selected range into multiple worksheets…
      http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=267&utilities=20&lang=en_us
       
      If you need additional help then please let me know,
      Kind regards,
      Bastien

      • Noah says:

        I realy like this macro.  I was just wondering how do you get the formulas to auto copy when information is added.

        Thanks,

        NN

    • Chandoo says:

      @Andrew… As Bastien pointed out, there must be something wrong with the way you have un-installed it. I have heard many people say nice things about ASAP utilities.
       
      PS: I have no association with them. Just want to make sure you & other readers get correct information.

  4. wintermute says:

    Andrew,

        I didn’ try that out ( I don’t use any of add-ins)
        but I’m sure you can easily switch it off from Add-Ins menu ( Excel options)

  5. Mawdo81 says:

    Hi,

    I’m sorry, I’m normally a fan of this blog but this time:
    - There really is no need to select, it is slow and cumbersome, it is much better practice to use:
    SourceRangeObject.copy destination:= DestinationRangeObject
    Or even better, if you have pre-formatted the areas:
    DestinationRange.Value = SourceRange.value

    It is even less useful to select and activate the individual sheets.

    Lastly, why are you encoraging the seperation of data like this?  It is much better to store the data in 1 place & then offer a report by Employee code, other wise future consolidation can be an issue if such reports aer required.

    Thanks,

    J

    • Christian V. says:

      Largely I’ll have to agree with J here.

      Regarding separation of data, I’m not sure I follow you J. The data is not separated, only copied. E.g. are  the separated sheets used as some form of reports which then could be e-mailed seperately to each employee? In order to talk of best practice it’s necessary to know the purpose of the sheet. 

      Oh, and where does the input come from? Is it necessary to actually enter it manually to a spreadsheet?

  6. Ron Wallace says:

    I have a workbook I created for a customer some months ago that does the same thing but with the following additions

    The user is able to select what I called the key column, in your case the Employee code, selected by name.  The macro then determines the column number for the key column.  Next, the unique values in the key column are determined and worksheets for each unique name are created and automatically given that name as the worksheet name. Next the data are parsed with each newly created worksheet filled only the records for that name.

    Obviously, my VBA code is a little longer than yours, but is a real time saver if you have 100 employee codes, for example.  Also the ability to use any heading is convenient. 

    • Confusedcelt says:

      What you have just described sounds exactly what I am looking for….

      I have a massive database for a entire school section, and I need to generate lists of subjects into their own tab, from one master sheet of options….

      If you can send the dummy report etc – I would be eternally grateful!!

      • Confusedcelt says:

        I am a real novice – only starting looking at this a couple of days ago – so keep it ‘simple’…

        cheers again
        Confusedcelt

  7. goss says:

    Hi Ron,

    Sounds very interesting, cany you share your code or workbook.
    I’ve used Advanced Filter in the past to burst my data into seperate sheets

    It could still be mre dynamic to make it better
    [1] Does the sheet already exist?
    ….If not, add the sheet, if yes, do we need to append the data or delete
    ….and add new?
    [2]The code assumes…
    …[A] First Row 1
    …[B] First Col A
    …[C] Last Col K
    Can we make all of these dynamic instead?

    [code]
    Sub ar_MoveServiceCharges()
        Dim wsData As Worksheet
        Dim wsServiceCharge As Worksheet
        Dim rngData As Range
        Dim rngCrit As Range
        Dim rngDest As Range
        Dim arrCrit
        Dim lngRows As Long
        
        Set wsData = Worksheets("Data")
        Set wsServiceCharge = Worksheets("ServiceCharge")
        wsServiceCharge.UsedRange.ClearContents
       
        lngRows = wsData.Range("A" & Rows.Count).End(xlUp).Row
        
        Set rngData = wsData.Range("A1:K" & lngRows)
        Set rngDest = wsServiceCharge.Range("A1")
        
        arrCrit = Array("Source Name", "Late Payment Invoice")
        Set rngCrit = wsData.Range("IV1:IV2")
        
        rngCrit = WorksheetFunction.Transpose(arrCrit)
        
        rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False
        
        rngCrit.ClearContents
       
        'Delete Service Charge Items
            ar_DeleteServiceCharges
       
        Set wsData = Nothing
        Set wsServiceCharge = Nothing
        Set rngData = Nothing
        Set rngCrit = Nothing
        Set rngDest = Nothing
        Set arrCrit = Nothing
        
    End Sub
    [/code]    

    • Ron Wallace says:

      I would need to share the workbook since the VBA code is easier to understand with the worksheets it refers to.

      I can’t send my client’s data, but I could create a version with dummy data.  The method is completely dynamic with regard to number of columns and rows.  The only restriction is the row number that contains the header.  With a little more coding that also could be made dynamic.  There is a user guide worksheet than allows the user to enter the key column name (column heading).  After that press the “GO” button and the process is completed.

      What is the method to upload workbooks to this site?  I have just started reading it. 

  8. Malvinder says:

    Help me in adding few conditions in the macro,

    Like, sheets should be automatically created and named on some condition which would be a better idea 

  9. Phil says:

    Well, this is nice and handy and just comes at the right time.
    Regarding copying of data I find the comments above not appropriate because you don’t know the purpose of the sheets. I am dealing with situations like this all the time and although I have ideas how this could be done better I have to accept the fact that some people simply want it this way and there is no need arguing with them. Therefore I see that this code of course has its right of existence, although this may not be the best way to handle the data.

  10. Mawdo81 says:

    @Christian,
    If all we are doing is copying the data & the original is still there, can yuo please explain to me what happens to the copied data when the original is changed or updated, or added to?

    If we use the original as a data store and have reports avaiable then the reports are always current.  If the reports need to be distributed then we can generate a copy not linked to the data.

  11. Khushnood Viccaji says:

    In the past I have also extracted data like this (to multiple sheets / workbooks) using VBA.

    Someone suggested using AdvancedFilter, which would do the job faster.
    I’d also sort the data on the key column first, and then use the advanced filter option.
    This makes it even faster while executing, especially when it involves a large number of records.

    Also, another easy (but often forgotten) thing is to add a couple of lines of code to suppress screen updates while the code is executing.
    This results in much faster execution of the code. 
    Application.Screenupdating = False at the beginning, and =True at the end of the code, will do the trick.

    Finally, if you’re using the AdvancedFilter option, then you don’t need to use complex cell / row selection code. 
    Simply use :     ActiveCell.CurrentRegion.SpecialCells(xlCellTypeVisible).Select  -or- .Copy and paste the data on the other sheet.

  12. Tori says:

    @Mawdo81

    I am really interested in learning cleaner techniques than I currently employ. Could you provide a short example of the technique to which you refer?

    I am teaching myself how to do things like this, and would love pointers on best practices. For example, this is code I wrote for our admins to use. They are frequently asked to slice data into separate workbooks, based on various entities (locations, areas, divisions, etc.). We are a healthcare company, so this assists us with keeping to HIPAA requirements when sending out worklists. Many of our users in the field still have Excel 2003, and the data is never >65,000, so the format used is Excel8.

    Oh, and I’m not asking you fix my code; it works well, and I am happy with it – and so are my admins! I’m just curious if I’m on the right path.

    The template has 3 worksheets:
    Instructions
    Data
    Criteria

    The Instructions sheet includes two user-entered fields: Pathname and Filename.

    The Data worksheet is just that – with the criteria being in column A.

    The Criteria worksheet is a unique listing of the values from the criteria column.

    The code is as follows:

    Public Sub SliceAndDice()
    On Error GoTo BAD_SLICE
    Dim rngCriteria As Range
    Dim strPath As String
    Dim strName As String
    Dim strPathName As String
    strPath = Sheets(“Instructions”).Range(“E3″).Value
    ‘Make sure something has been entered into the Pathname
    If IsEmpty(strPath) Then
        MsgBox “A path is required; please enter and rerun macro”
        GoTo GOOD_SLICE
    End If
    ‘Clean up the named ranges – just in case they’ve gotten squirley
    ‘(sometimes, copying/pasting/deleting things causes the named range formulas to adjust – we can’t have that)
    ‘The macro will error if this name doesn’t exist
    On Error Resume Next
    ActiveWorkbook.Names(“Extract”).Delete
    ‘Return to normal error checking
    On Error GoTo BAD_SLICE
    ‘Create the named ranges for the data worksheet and the criteria worksheet
    ActiveWorkbook.Names(“Data_Range”).RefersTo = “=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))”
    ActiveWorkbook.Names(“Criteria_Range”).RefersTo = “=OFFSET(Criteria!$A$1,0,0,COUNTA(Criteria!$A:$A),1)”
    ‘Turn off the screen updating and dialog boxes – macro runs faster and doesn’t freak out users with crazy questions
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ‘The user is allowed to include a specialized name to go along with the auto naming in the workbook
    ‘Example: If the user typed “March Follow Ups”, then the file names would be yyyy-mm-dd March Follow Ups ‘Slicing Entity’
    strName = Sheets(“Instructions”).Range(“E6″).Value
    ‘Loop through the criteria
    For Each rngCriteria In Sheets(“Criteria”).Range(“A1″).CurrentRegion.Cells
       
        ‘Filter the data
        Sheets(“Data”).Range(“Data_Range”).AutoFilter Field:=1, Criteria1:=rngCriteria
        ‘Select the Data worksheet
        Sheets(“Data”).Select
       
        ‘Just to make sure we’re in the right place
        Range(“A1″).Select
       
        ‘Copy the filtered data
        Selection.CurrentRegion.Select
        Selection.Copy
        ‘Paste the data into a new workbook
        Workbooks.Add
        ActiveSheet.Paste
       
        ‘Autofit the columns
        With Cells
            .Select
            .EntireColumn.AutoFit
        End With
       
        ‘Rename the worksheet
        Sheets(“Sheet1″).Name = rngCriteria
        ‘Freeze the top row
        Range(“A2″).Select
        ActiveWindow.FreezePanes = True
        ‘Turn off Cut/Copy Mode (removes the ‘marching ants’)
        Application.CutCopyMode = False
       
        ‘Create the full Path Name
        strPathName = strPath & “\” & Format(Date, “yyyy-mm-dd”) & ” ” & strName & ” ” & rngCriteria
       
        ‘Save the workbook in Excel 97/2003 format
        ActiveWorkbook.SaveAs Filename:=strPathName, FileFormat:=xlExcel8
       
        ‘Close the workbook
        ActiveWindow.Close
        ‘Remove the filter
        Sheets(“Data”).ShowAllData
    Next
    MsgBox “Extraction Complete”, vbOKOnly, “Party on, Wayne!!!”
    GOOD_SLICE:
        ‘Turn screen output back on and allow dialog boxes again
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Sheets(“Instructions”).Select
        Exit Sub
       
    BAD_SLICE:
        If Err.Number = 1004 Then
            MsgBox “No criteria found. Please correct and reun the macro.”, vbCritical + vbOKOnly
        Else
            MsgBox Err.Number & vbCrLf & Err.Description
        End If
        Resume GOOD_SLICE
    End Sub

  13. kazshak says:

    Just wondering why not use Faseeh’s formula that Chandoo did the forensic on back on March 1, 2012.  That way you don’t have to use VBA.  Can’t you just use array formulas on the individual sheets that are something like this:

    {=IFERROR(OFFSET($C$2,SMALL(IF((C2:C11)=2,ROW(C2:C11),FALSE),ROW(A1))-2,1),”…”)}

    And then just copy it down for how many ever rows you need; and change the “=2″ to be “=1″ or “=3″ for the respective sheet.

    Anybody have any comments about using this approach instead?

  14. daniel says:

    Hi,
    Thanks for this macro.
    It’s exactly what i was looking for.
    However, I’m having a bit of an issue when I use it.

    When copying from the original sheet to the others, the macro copies from column B and on.. it doesn’t copy column A.

    Any help would be very appreciated.

    Regards 

  15. Oliver says:

    Run Time error = Employee code increased to 4, no extra sheet has been created.
    Can someone help me to use this superb file correctly for my needs.
    Thanks

  16. Oliver says:

    Thanks, no macro available to do this automatically ?

  17. tvrao says:

    sir,
    we need to create a file (WB) based on cell value and transfer the data based on cell value or for every change in cell value OR to new sheet named after the cell value ( to create a sheet if not existing)
    such code would be comprehensive.
    t.v.rao
    cm, ro, ubi, mehsana

  18. Dante says:

    Hi Chandoo,

    Am just learning VBA.

    Which part of the code that says that if there’s a change in employee code, corresponding data shall be copied to such employee (code) sheet?

    Thank you.

  19. Rahul Jain says:

    I have one excel file wherein i have alphanumeric data in cells A1:A85 & B1:B85. (In Sheet 1)

    I have another 85 sheets in the file. I want to pull data of A1 into D20 & data of B1 into E20 (In Sheet 2)

    I want to do these in all the 85 sheets in sequential order. i.e. Sheet 2 should pull data of A1 & B1(Sheet 1), whereas Sheet 3 should pull data of A2 & B2(From sheet 1), and Sheet 4 should should pull data of A3 & B3(from Sheet 1) and so on.

    Can you please help me with this?

    Thanks in advance.

  20. Rahul Jain says:

    Hi Hui,

    Thanks for replying.

    I have successfully completed my task using a VB code.

    • TJ says:

      Hi Rahul

      I am also looking for something similar. Is it possible for you to provide me with the VB Code?

      Your help is much appreciated.

      Thanks

  21. BRAJ says:

    Please send the VBA code Break the sheet into multiple workbooks by State, District, Region & zone wise at a time same data.

  22. Jim Horg says:

    This is simple transaction processing. Vijay’s solution works ok albeit somewhat slow (select,copy,paste) and difficult to follow.

    A more straightforward approach is to use Tables aka ListObjects. Just rename the 4 Tables DataEntry, Table1, Table2 and Table3 and …

    Sub DataEntryToEmployee()
    Dim oSrcTable, oDestinationTable As ListObject, oListRow, oNewRow As ListRow, sEmployeeCode As String

    Set oSrcTable = Sheets(“Data entry”).ListObjects(“DataEntry”)

    For Each oListRow In oSrcTable.ListRows
    sEmployeeCode = oListRow.Range.Cells(3)
    Set oDestinationTable = Sheets(sEmployeeCode).ListObjects(“Table” & sEmployeeCode)
    Set oNewRow = oDestinationTable.ListRows.Add
    oListRow.Range.Copy Destination:=oNewRow.Range
    Next oListRow

    End Sub

  23. John-Paul says:

    So I have tried to manipulate this code for the same puprpose, but with column E as my designator to sheets. the code looks like this:
    Sub copyPasteData()
    Dim strSourceSheet As String
    Dim strDestinationSheet As String
    Dim lastRow As Long

    strSourceSheet = “Sheet1″

    Sheets(strSourceSheet).Visible = True
    Sheets(strSourceSheet).Select

    Range(“E2″).Select
    Do While ActiveCell.Value “”
    strDestinationSheet = ActiveCell.Value
    ActiveCell.Offset(0, -4).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
    Selection.Copy
    Sheets(strDestinationSheet).Visible = True
    Sheets(strDestinationSheet).Select
    lastRow = LastRowInOneColumn(A)
    Cells(lastRow + 1, 1).Select
    Selection.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Sheets(strSourceSheet).Select
    ActiveCell.Offset(0, 4).Select
    ActiveCell.Offset(1, 0).Select
    Loop
    End Sub
    (basically the same but modified source sheet and the cell offset)However, It gives me a compile error on LastRowInOneColumn. If i remove (A) from the line it will give me the last entry for each variant in column E in the various worksheets. I am unable to figure this part out. Please help how I should correct it. Thanks so much in advance!

    • Gary Skelton says:

      Hi,

      I think that you need an integer for that.

      Column A would be 1.

      Regards

      Gary

      • John-Paul says:

        Well I tried that, and still no luck. So far the only thing that has gotten ANY results was removing the whole “(A)”, but then it gave an incomplete data set. Any further help would be much appreciated!

  24. cve_ENG says:

    HELLO EVERYONE
    can anyone help me with this problem
    can you look first at the pic http://i.imgur.com/frIGU7o.png
    I need the values of cells in column O and P take their values according to the numbers in the column L
    for example
    cell L3 have the value 16 then in cell o3 the value will be .56 and in cell P3 the value will be 0
    another example cell L4 have the value 11 then in cell O3 the value will be .37 and in cell P3 the value will be 0

  25. truth447 says:

    is there any way to display a message box if there is not a tab for an employee code. For instance you add employee code 4, but forgot to creat ethe tab for it?

Leave a Reply