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

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
 

Attachments

  • Referral Program Tracking solved by formula.xlsx
    215.1 KB · Views: 8
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.
 
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
 
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
 

Attachments

  • by-B-column.png
    by-B-column.png
    11.4 KB · Views: 14
  • by-L-column.png
    by-L-column.png
    12.5 KB · Views: 12
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.
 
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 [COLOR=#ff0000][B]>=[/B][/COLOR] "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
 

Attachments

  • Referral Program Tracking VBA.xlsm
    86.2 KB · Views: 5
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.
 
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 [COLOR=#ff0000][B]>= 6[/B][/COLOR] Then
Now, on the worksheet "6 Referral" automatically you have all the numbers/rows that are equal and greater than 6
See attachment
 

Attachments

  • Referral Program Tracking VBA.xlsm
    84.4 KB · Views: 6
Sir, This solution is for ID column only i think. How can i put criteria for Column L (Referred By)?
 
Copy Rows if condition match to multiple sheets

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
 

Attachments

  • Referral Program Tracking VBA - finished.xlsm
    90.3 KB · Views: 6
Genius!!! It is working. May God Bless you for your continued efforts to help me out in solutions. Knowledge is a power.

Regards

Shehbaz H.
 
Back
Top