fbpx
Search
Close this search box.

VBA Move data from one sheet to multiple sheets

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

74 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!

        • kaushal says:

          Hi I an using the similar code but I am unable to run it as its giving a compilation error would request if you explain how did you rectify the following command line:
          lastRow = LastRowInOneColumn (A)

  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?

  26. alam says:

    Hi, It works great but I want this to copy 19 cells from left not -2 unlike your sheet

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

    But it copies only first two rows than gives me error

    Runtime Error: 1004
    Application-Defined or object defined error

    • Ocean says:

      Alam,

      Change the code from
      ActiveCell.Offset(0, 2).Select
      ActiveCell.Offset(1, 0).Select
      to
      ActiveCell.Offset(0, 19).Select
      ActiveCell.Offset(1, 0).Select

  27. Mandeep says:

    How it is checking the unique values ??? and seperating ??

  28. dev65 says:

    Dear all, i have code vb for export to excel :
    With xlWorkBook
    .sheets("Sheet1").Name = "test1"
    .sheets("Sheet2").Name = "test2"
    End with

    How is that possible to customize column in each sheets?
    For example,
    - in sheet test1 i want column will be appear : name, class, course
    -in sheet test2 i want column will be appear like : score, absent, teacher.
    Should i use if else? Can you teach me where should I put if else, and how the code? Thank you in advance.

  29. Jay says:

    I have a report that is created everyday that I copy and paste into an excel spreadsheet and then copy and paste by criteria ( in my case a number used to represent a county name i.e. 1 = Abbeville ). So I highlight and copy all data in column A that also has value "1" in column B. I think it would be much easier if I could copy and paste this list into the first tab in my spreadsheet, and then have all the data filtered into the correct tabs by county code. I am very new to this, but I am confident I can follow simple instructions. I have pre-existing data in these tabs that I don't want to be overwritten, but instead to be added to the next available cell to continue the list. I don't understand how to do this on a scale of 46 different tabs. (I have 7 digit numbers in column A and 1-91 only odd numbers for column B). Any suggestions? It is so time consuming to have to copy and paste 46 different times every day.

  30. Paul Healey says:

    This works for what i need to do but would like to take what is copied and have it paste the data into another location on the other spreadsheet.
    Example
    copying data from A1 through E1
    would like it to paste in another spreadsheet into A8 through E8

    Right now it puts the date in A2 through E2

  31. Ahsan says:

    Dear all, I have a database. I copy the respected information about an employee and paste in the respected sheet on the basis of his/her field of education, like if field is Finance the data of that employee will go to the Finance sheet and so on.., I have to do it for many times due to bulk data. I need a VBA Code that may do it automatically.
    One more thing the data of employee is on 10 number of rows.
    I need a code that look for value (Finance ) in the education field. (One by one for each employee) if the value is there then copy the 10 rows of the data and paste in the Finance sheet. and there are number of fields of education Finance, HRM, Engineering, Mass Communication, ect.
    Thanks all.

  32. subhajit says:

    Hello,
    pls help in VBA code ..
    sheet2 (A2:F23) tabel data come in sheet1(A2:F4) , 3 by 3 step in vba code for Spin button ..

  33. Abhijeet says:

    Sir,
    Can you give me a macro code to move Data from One workbook to another workbook using user form. i.e. I want to copy some cells from Book1.xlsx to rows of Book2.xlsx. Source cells of Book1.xlsx are constant but Destinations cells in Book2.xlsx are different, i.e. data must be copy in empty row of Book2.xlsx.
    Please give me a code. It very important for me.

  34. sadaqat says:

    I have diffrent types of data in 1 sheet i.e. row no 1 to 20 have diffrent data and 21 to 40 hve diffrent data & i want to copy row no 1 to 20 and 21 to 40 in to seprate worksheets how can i do this

  35. sadaqat says:

    I have diffrent types of data in 1 sheet i.e. row no 1 to 20 have diffrent data and 21 to 40 hve diffrent data & i want to copy row no 1 to 20 and 21 to 40 in two seprate worksheets how can i do this

  36. Fanuel says:

    Hi Guys
    I want to set up a VBA formula that populates a summary from 2 worksheets:
    1 - Income worksheet shows 3 columns, B "Source", C "Product" and D " Income".

    2 - Cost worksheet shows 3 columns, B "Source", C "Product" and D " Cost".
    3 - Summary worksheet should summarize the amount of total income "column C" and cost "Column D" per product listed on Column B based on match of Source selected B2 with Column B under worksheets " income" or "cost".
    It works with excel formula:

    =SUMPRODUCT((B$2=Income!$B$3:$F$50000)*(Income!$D$3:$D$50000)) for incomes and

    =SUMPRODUCT((B$2=Cost!$B$3:$F$50000)*(Cost!$D$3:$D$50000)) for costs. But i would like to have it in VBA.
    I will appreciate any help

  37. balwant sharma says:

    First add counting from 1 to 10 till last row in column B Every time it should create New Workbook Then create 1 to 10 tabs in that book copy the respected data on respected tabs

  38. Pravin says:

    Dear Sir,

    I want to copy data from A1, E3 & L7 of Sheet1 to H4, N9N & P11 of Sheet2. But formula should not be copied .....Sir, I have toiled hard to search such a formula but I could not be succeeded....So would you frame such vba code in excel and send me to my mail id i.e. bhaiswarpravin@gmail.com....Thanks and kindest regards sir.

  39. Sam says:

    Following Suresh's problem, do you have a VBA code if there are 2 differences:
    1. i have 2 criteria (columns E and F)
    2. my sheets are not named exactly like the criteria

  40. Shawn says:

    Hi so I am trying to utilize this script for my organization on an excel based Employee phonebook in one Workbook that was manually created. I have one Master List setup on the 1st Worksheet and then I have 3 separate worksheets that are based on Geographic Region (County) that towns are in. on the Master List I have all sorts of Different Towns, Some Counties have Multiple Towns in them I want to be able to have the Masterlist automatically filter to the Sublists based on what county the town is in and do so everytime it is updated with new information also I would like it if the Master list has names removed from it it will also do the same in the corresponding Sublist

  41. Imtiyaz Ahmad Khan says:

    With regards-some specified data whether all along or with some break/s
    could also exist there into each personal separate ledger account excel files or multiple? as an self Auto reflection from continuing main master excel sheet say cash book.This is not a fixed nature (just see manual working at cash book we have to maintain ledger each accounts).So only by working at main excel sheet the data should also exist automatically at each personal ledger files.Thus by doing this saving extra efforts of copy paste posting.How, please
    Imtiyaz Khan

  42. Diana says:

    Hi, i want to ask. How to split data from one master worksheet to multiple sheets on another new workbook? For split data on same workbook, i already done just i dont know for another workbook.

    And is there any simple macro for put inputbox for this kind of code.

    Set ws = sheets("sheet1")

    Im sorry im really a beginner of this macro n vba thing.

  43. Satyajeet says:

    Hi,
    i want to sort by column B instead of column C as per your example, After changing in the code but it gives error.

    Please reply

  44. Denitsa says:

    Hello,

    Can you please help me with a code for following actions:

    There is an excel file with existing sheets and information in the first three columns in every sheet. The name of the sheets are codes of offices.
    I have another excel where I have information in columns D,E,F. In column D are the codes of the offices and in the other two columns is the additional info that I have to copy in the first file again in column D,E and F. The criteria is the code of office.

    I will be very thankful if you can help me.

  45. PKB says:

    Hi,

    I am using a code to split tabs into new WBs. The issue is the formulas stop working on the new WBs as the refrence sheet is still in the original sheet. Is there a solution for it

    the Code is:
    Sub CreateNewWBS()
    Dim wbThis As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim strFilename As String

    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
    strFilename = wbThis.Path & "/" & ws.Name
    ws.Copy
    Set wbNew = ActiveWorkbook
    wbNew.SaveAs strFilename
    wbNew.Close
    Next ws
    End Sub

  46. Ray Rzepecki says:

    I am trying to write a script where I copy data from 2 workbooks, paste into my master file and update my pivot tables based on the new data to create an invoice.
    VB code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+e
    ' ***** From file called May 2018 - Vendor Energy - 6.15.18 - Invoice.xlsx Tab Sheet1 to current file Tab called Sheet22 *****
    '
    '
    Workbooks.Open Filename:= _
    "C:\Users\rzepeckir\Documents\Vendor Current Month\May 2018 - Vendor Energy - 6.15.18 - Invoice.xlsx"
    Selection.AutoFilter
    ActiveCell.Offset(-17, -6).Range("A1:P224").Select
    Selection.Copy
    Windows("VBA Extractor r57with code.xlsm").Activate
    'ActiveCell.Offset(-1, -1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    End Sub

    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+f
    '
    '***** From file called Copy of Vendor Master Pole set File 06-12-18.xlsx Tab Sheet1 to current file Tab called Sheet20 *****
    '
    '
    Workbooks.Open Filename:= _
    "C:\Users\rzepeckir\Documents\Vendor Current Month\Copy of Vendor Master Pole set File 06-12-18.xlsx"
    Selection.AutoFilter
    ActiveCell.Offset(0, 0).Range("A2:AQ40000").Select
    Selection.Copy
    Windows("VBA Extractor r57with code.xlsm").Activate
    'ActiveCell.Offset(-1, -1).Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    End Sub

    ' Refresh all applicable pivot tables to setup month's data

    Sub refresh()
    '
    ' refresh Macro
    ' refresh data
    '
    ' Keyboard Shortcut: Ctrl+r
    MsgBox ("Update may take several minutes, Click Ok to begin")

    ' replace with Refresh All (had to remove the check for Enable Background refresh to make it wait)

    ActiveWorkbook.RefreshAll

    ' Refresh all Pivot tables in all worksheets

    Dim shtTemp As Worksheet
    Dim pvtTable As PivotTable

    For Each shtTemp In ActiveWorkbook.Worksheets
    For Each pvtTable In shtTemp.PivotTables
    'pvtTable.RefreshTable
    Next
    Next
    MsgBox ("Update Complete,All data is Up-to date")
    End Sub

    Public Function save()

    End Function
    //////End VB Code
    I am trying to make this run through once and then save the file with the updates.
    Any help would be greatly appreciated.
    Thank you,
    Ray

  47. Asnawi says:

    I am beginner in VBA for excel (Using Excel 365) I just copy and run the above vba code but have a compile error: sub or function not defined in
    lastRow = LastRowInOneColumn("A")

    any suggestion regarding that error? thank you.

    BR

    Asnawi

  48. Prabhat says:

    Dear Chandoo,

    No Doubt, Code is perfect. but how to skip if duplicate raw found in those sheets where data are moving?

Leave a Reply