Specific range of data copy from one sheet to another sheets.

  • Dear all,
    For my work , I am using four sheets (“input”, “output1”, “output2” & “output3”). Here, I am having some data in the CELL A & B of sheet “input”. Now based on condition I want to copy Specific range(CELL B) of data from “input” sheet to another three sheets like “output1”, “output2” & “Output3”. Here is an example data in the sheet ”input”.


    CELL A CELL B
    output1 Apple
    output1 Orange
    output2 2KG
    output2 1KG
    output2 10KG
    Output3 Fish
    Output3 Rice
    Output3 Banana

    Here is the condition 1:


    CELL A those having text “output1” & along the range data of CELL B need to copy & paste to the sheet “Output1”


    Condition 2:
    CELL A those having text “output2” & along the range data of CELL B need to copy & paste to the sheet “Output2”


    Condition 3:
    CELL A those having text “output3” & along the range data of CELL B need to copy & paste to the sheet “Output3”


    So finally , we will get data in the sheet “output1” like below


    CELL A
    Apple
    Orange


    And like wise, we will get data in the sheet “output2” like below
    CELL A
    2KG
    1KG
    10KG


    And same way for the sheet “output3”
    CELL A
    Fish
    Rice
    Banana

  • There are a number of ways to accomplish your goal. This is one :


  • Hi Carim ,
    Thanks for your replay , based on my query , your formula is working fine. But I need macro solution as my data range are not fixed. It’s about more than 70k+/- data range. It will help me if I get macro solution.

  • Hi Logit ,
    All of your three macro codes for copy particular range of data from one sheet to another sheets without any condition but my query was different . actually I wanted that , data will move to another sheet based on text in CELL A of “Input” sheet. However many thanks for trying to help me.

  • Hi Carim ,
    Thanks for your replay , based on my query , your formula is working fine. But I need macro solution as my data range are not fixed. It’s about more than 70k+/- data range. It will help me if I get macro solution.


    For a very large database, it is quite important to optimize the process ...


    As a consequence, to ' Explode ' your data into individual sheets, you should rely on AutoFilter to speed up your macro ...


    As soon as I have a moment, I will check my personal archives ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi again,


    Below is the Explode Data macro ... which needs to be adjusted to your own specific requirements ...



    Hope this will help ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,
    I will implement your code for my cases , although I am not good in macro. If further help required I will let you know. however


    I was trying myself by below code but it copy only CELL A data but I need CELL B data ! is it possible to implement one more code so that it can copy CELL B data to the target sheet.



  • Once you have tested the macro posted in message # 7 ...


    Feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!