Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 31

Thread: Copy of rows from one sheet to another sheets based on certain criteria

  1. #21
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by fairchance View Post
    I have spreadsheet with column ID. I want to copy data from Raw data sheet to other six sheet on the following criteria:........................Kindly solve my problem and return the same after solution.
    If I understood you, I have solved the problem using the formula. It might help this my idea

    On the worksheet Raw_Data I have added an extra column in front of your data
    The cell 'A1' I put a the drop down list that allows you pick a column as a condition
    The cell 'A2' I put a formula that compared the condition as a result returns the number of repetitions of data from the same row in column conditions. Copy the formula to the last row

    On the other worksheets, where you want to have results (1_referral, 2_refeerral etc ...) I also added an assist column at the beginning.
    In cell 'A1' I put a formula that automatically returns a number from the name of the worksheet (from tab sheet)
    In cell 'A2' I put a as a result of a formula that returns data for the defined condition in a cell 'A1'. Copy this formula to the right until the last column and then down

    See my example files
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  2. #22
    Bundle of Thanks Sir, I need slight improvement:
    1. If the records are more than six numbers them may be go under 6_Referral sheet
    2. 2_Referral sheet is not working

    Kindly check

    Regards

    Shehbaz H.

  3. #23
    Sir Navic,

    I appreciate your solution which solve my purpose dynamically. I am waiting for your reply with revised reply. Always be happy!!!

    Kind Regards

  4. #24
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    1. In this case and in this way NO. Simply, you create additional worksheets for more numbers. Perhaps some expert in Excel could do.
    2. I do not know why you do not work "2_Referral" sheet. For me it work. See screenshots

    First image, condition is B column
    Second image, condition is L column
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	by-B-column.png 
Views:	10 
Size:	11.4 KB 
ID:	3699   Click image for larger version. 

Name:	by-L-column.png 
Views:	11 
Size:	12.5 KB 
ID:	3700  
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #25
    Thank you for reply. Sir, if a record is repeated 100 time we can not create additional sheets upto 100 that is why it is not requirement. If any record is repeated more than six time then it will move to 6_Referral. Moreover, is the records may be sort before moving to all six sheets?

    Kind Regards

    Shehbaz H.

  6. #26
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by fairchance View Post
    If any record is repeated more than six time then it will move to 6_Referral.
    I'm trying to help you. But I do not have enough knowledge in VBA programming.

    See this example. I put a VBA code but there is a problem, and I do not know how to solve. VBA code displays only some values greater than 6
    The problem is in the code ">= 6", this not working good
    Code:
    Sub CopyRowsWithCondition6()
    'Copy Rows Data for Condition in A column
    Dim i, LastRow
    LastRow = Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Row
    Worksheets("6 Referral").Range("A2:S100").ClearContents
    For i = 2 To LastRow
    If Sheets("Raw Data").Cells(i, "A").Value >= "6" Then
    Sheets("Raw Data").Cells(i, "A").EntireRow.Copy Destination:=Sheets("6 Referral").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
    Next i
    End Sub
    Maybe some expert VBA it could resolve.
    My apologies, but I can not continue, because I have no idea

    btw: pay attention to the A column set new formulas in the worksheet Raw Data
    my regards
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #27
    I am still wandering to get solutions but not achieved. You are referring cross post. Who will provide my solutions??? Is no any expert available in this forum to solve my problem? Please arrange to resolve my issue.

    Shehbaz H.

  8. #28
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by fairchance View Post
    I am still wandering to get solutions but not achieved.
    I think I solved your problem.
    Instead this lines of code
    Code:
    If Sheets("Raw Data").Cells(i, "A").Value >= "6" Then
    You need to put this line of VBA code
    Code:
    If Sheets("Raw Data").Cells(i, "A").Value >= 6 Then
    Now, on the worksheet "6 Referral" automatically you have all the numbers/rows that are equal and greater than 6
    See attachment
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  9. #29
    Sir, This solution is for ID column only i think. How can i put criteria for Column L (Referred By)?

  10. #30
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013

    Copy Rows if condition match to multiple sheets

    Quote Originally Posted by fairchance View Post
    How can i put criteria for Column L (Referred By)?
    I am again modified file.

    - In cell A1 on the worksheet "Raw Data" is the Data Validation. Using the drop down list you can choose column as a condition.
    - In A2:A297 range is a new formula, which is linked to cell A1. This is an important formula, because it counts, occurrence conditions
    - VBA button "Clear Range" cleans all the cells in a certain range to other worksheets
    - VBA button "Copy with condition" cleans all the cells on other worksheets and then copy the rows that meet the condition
    - On to all other worksheets you can hide 'A' column (if you want)
    - On to all other worksheets, there is VBA button if you want to copy individual data

    I hope that this final action, my respect and greetings from sunny Croatia
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •