Copy data from one worksheet to another using spec criteria [Solved/Closed]
Siobahn
Moderator Last seen
February 6, 2019
- Jul 29, 2017 at 07:04 AM
- Posts
- 2
- Registration date
- Thursday July 6, 2017
- Last seen
- July 7, 2017
- Posts
- 1233
- Registration date
- Thursday July 24, 2014
- Status
Related:
- Excel copying data from one sheet to another
- Macro to pull data from another worksheet based on criteria - How-To - Excel
- Transfer specific data from one worksheet to another for reports ✓ - Forum - Excel
- Split data into multiple worksheets excel 2007 - How-To - Excel
- How to copy data from one worksheet to another in excel ✓ - Forum - Excel
- Transfer data from one worksheet to another in excel - How-To - Excel
6 replies
Best answer
- Posts
- 1233
- Registration date
- Thursday July 24, 2014
- Status
Hello Siobahn,
Try the following code, placed in a standard module and assigned to a button:-
You will need to remove all conditional formatting from the Master sheet first.
I have interpreted this to mean any cell in Column P that is blank or has an "N" placed in it.
Following is the link to your file with the code implemented:-
https://www.dropbox.com/s/03ncdbcmyqsotxs/Siobahn%28If%20block%20statement%29.xlsm?dl=0
Click on the "RUN" button to see it work.
I hope that this helps.
Cheerio,
vcoolio.
Try the following code, placed in a standard module and assigned to a button:-
Option Compare Text
Sub TransferData()
Dim lr As Long
Dim i As Integer
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lr
If Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 45 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 70 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value >= 90 Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 4
End If
If Cells(i, 16).Value = "" Or Cells(i, 16).Value = "N" Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 6
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
You will need to remove all conditional formatting from the Master sheet first.
Also, if the column for keys has not been marked as received, I want the row to be highlighted yellow on the master sheet (sheet1)
I have interpreted this to mean any cell in Column P that is blank or has an "N" placed in it.
Following is the link to your file with the code implemented:-
https://www.dropbox.com/s/03ncdbcmyqsotxs/Siobahn%28If%20block%20statement%29.xlsm?dl=0
Click on the "RUN" button to see it work.
I hope that this helps.
Cheerio,
vcoolio.
A few words of thanks would be greatly appreciated. Add comment
4507 users have said thank you to us this month
- Posts
- 1233
- Registration date
- Thursday July 24, 2014
- Status
Hello Siobahn,
If you have deleted a column within your data set then, yes, you'll receive an error. If you delete a column outside your data set, nothing will change.
On deleting a column, all the other columns then move to the left by the number of columns that have been deleted. The code will then be searching for a criteria that is no longer there. For example, Cells(i, 7) may have a criteria required by the code but then you delete Column F(6) so Cells(i, 7) now becomes Cells(i, 6).
You may be better off to leave it blank if the data in the column is no longer needed or hide it. Test it with a blank or a hidden column instead of deleting and let us know what happens.
Yes, regular formatting should be fine.
Cheerio,
vcoolio.
If you have deleted a column within your data set then, yes, you'll receive an error. If you delete a column outside your data set, nothing will change.
On deleting a column, all the other columns then move to the left by the number of columns that have been deleted. The code will then be searching for a criteria that is no longer there. For example, Cells(i, 7) may have a criteria required by the code but then you delete Column F(6) so Cells(i, 7) now becomes Cells(i, 6).
You may be better off to leave it blank if the data in the column is no longer needed or hide it. Test it with a blank or a hidden column instead of deleting and let us know what happens.
Yes, regular formatting should be fine.
Cheerio,
vcoolio.
- Posts
- 1233
- Registration date
- Thursday July 24, 2014
- Status
Hello Siobahn,
I don't quite follow.
As per your opening post, you wanted:-
- When a home is sold and it has a client name in Column F (not "SPEC" or Sales Center" in Column F) and is at 45% then the relevant row of data is copied to sheet 2. This is covered by the following part of the code:-
- When a home is sold and it has a client name in Column F (not "SPEC" or "Sales Center" in Column F) and is at 70% then the relevant row of data is copied to sheet3. This is covered by the following part of the code:-
- When a home is sold and is at greater than or equal to (>=) 90% and Column F is not showing "SPEC" or "Sales Center" then the relevant row of data is high-lighted green on the main sheet. This is covered by the following part of the code:-
As for Column P (keys), I refer you to your comment in your opening post:-
and my assumption in my post #1 which you did not clarify:-
So I now assume that you mean only if a cell has "N" in Column P you would like the relevant row of data high-lighted yellow in the main sheet.
A slight alteration to line 29 in the code in my post #1 should fix this for you as follows:-
Here is the code again with the slight modification:-
and following is the link to your file with the modified code implemented. You will see that it works as per your original explanation:-
https://www.dropbox.com/s/03ncdbcmyqsotxs/Siobahn%28If%20block%20statement%29.xlsm?dl=0
I hope its all clear now for you.
Cheerio,
vcoolio,
I don't quite follow.
As per your opening post, you wanted:-
- When a home is sold and it has a client name in Column F (not "SPEC" or Sales Center" in Column F) and is at 45% then the relevant row of data is copied to sheet 2. This is covered by the following part of the code:-
If Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 45 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
- When a home is sold and it has a client name in Column F (not "SPEC" or "Sales Center" in Column F) and is at 70% then the relevant row of data is copied to sheet3. This is covered by the following part of the code:-
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 70 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
- When a home is sold and is at greater than or equal to (>=) 90% and Column F is not showing "SPEC" or "Sales Center" then the relevant row of data is high-lighted green on the main sheet. This is covered by the following part of the code:-
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value >= 90 Then Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 4
As for Column P (keys), I refer you to your comment in your opening post:-
Also, if the column for keys has not been marked as received, I want the row to be highlighted yellow on the master sheet (sheet1)
and my assumption in my post #1 which you did not clarify:-
I have interpreted this to mean any cell in Column P that is blank or has an "N" placed in it.
So I now assume that you mean only if a cell has "N" in Column P you would like the relevant row of data high-lighted yellow in the main sheet.
A slight alteration to line 29 in the code in my post #1 should fix this for you as follows:-
If Cells(i, 16).Value = "N" Then
Here is the code again with the slight modification:-
Option Compare Text
Sub TransferData()
Dim lr As Long
Dim i As Integer
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lr
If Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 45 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 70 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value >= 90 Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 4
End If
If Cells(i, 16).Value = "N" Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 6
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
and following is the link to your file with the modified code implemented. You will see that it works as per your original explanation:-
https://www.dropbox.com/s/03ncdbcmyqsotxs/Siobahn%28If%20block%20statement%29.xlsm?dl=0
I hope its all clear now for you.
Cheerio,
vcoolio,
Good morning!
I apologize for being unclear. First, your assumption on the keys was correct. Blank or "n" both suffice for that portion. What I failed to include was that I only want that portion of code to apply to homes that are 70% or higher. Currently, it applies to ANY home that has the space marked blank or "n". I have tried to just "copy and paste" the code you used for that condition above but it does not seem to work.
This is what I ended up with:
But it does not work as I had hoped.
I apologize for being unclear. First, your assumption on the keys was correct. Blank or "n" both suffice for that portion. What I failed to include was that I only want that portion of code to apply to homes that are 70% or higher. Currently, it applies to ANY home that has the space marked blank or "n". I have tried to just "copy and paste" the code you used for that condition above but it does not seem to work.
This is what I ended up with:
If Cells(i, 16).Value = "" Or Cells(i, 16).Value = "N" And Cells(i, 7).Value >= 70 Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 6
End If
But it does not work as I had hoped.
- Posts
- 1233
- Registration date
- Thursday July 24, 2014
- Status
Hello Siobahn,
I don't see why it won't work as the syntax is correct. Perhaps you pasted it in the wrong place.
Following is the entire code again including the slight amendment for Column G:-
Copy and paste this entire code into a standard module in the sample work book that you supplied in your opening post. Remember to remove any conditional formatting and unmerge any merged cells.
Let us know what happens.
Cheerio,
vcoolio.
I don't see why it won't work as the syntax is correct. Perhaps you pasted it in the wrong place.
Following is the entire code again including the slight amendment for Column G:-
Option Compare Text
Sub TransferData()
Dim lr As Long
Dim i As Integer
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lr
If Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 45 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value = 70 Then
Range(Cells(i, 2), Cells(i, 28)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
ElseIf Cells(i, 6).Value <> "SPEC" And Cells(i, 6).Value <> "SALES CENTER" And Cells(i, 7).Value >= 90 Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 4
End If
If Cells(i, 16).Value = "" Or Cells(i, 16).Value = "N" And Cells(i, 7).Value >= 70 Then
Range(Cells(i, 1), Cells(i, 28)).Interior.ColorIndex = 6
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Copy and paste this entire code into a standard module in the sample work book that you supplied in your opening post. Remember to remove any conditional formatting and unmerge any merged cells.
Let us know what happens.
Cheerio,
vcoolio.
Thank you for your patience and help! I took out the "greater than" portion of the condition to simply have 70% items highlight and the code works!
***EDIT*** The problem I am finding now is that it is no longer copying the first column. Everything in the code states to start with column A, but it is not doing so. Column "B" is beign pasted in Column A and the data is ended up short a line. There are no merged cells, and I have removed all conditional formatting. I have simply copied and pasted the formula. I am unsure of why it now will not read the data correctly.
Not that you have to code this for me, just curious as to whether it is possible. Is there a way to have the items being copied to 45% and 70% sheets REPLACED instead of just added each time I click? (Like if I click it twice accidentally, instead of having it repeat, just have it "reset" the list as a whole. Not sure if I want it to do this, but wanted to know if it was possible.
Again, I really appreciate your help, thank you!
B. Siobahn
***EDIT*** The problem I am finding now is that it is no longer copying the first column. Everything in the code states to start with column A, but it is not doing so. Column "B" is beign pasted in Column A and the data is ended up short a line. There are no merged cells, and I have removed all conditional formatting. I have simply copied and pasted the formula. I am unsure of why it now will not read the data correctly.
Not that you have to code this for me, just curious as to whether it is possible. Is there a way to have the items being copied to 45% and 70% sheets REPLACED instead of just added each time I click? (Like if I click it twice accidentally, instead of having it repeat, just have it "reset" the list as a whole. Not sure if I want it to do this, but wanted to know if it was possible.
Again, I really appreciate your help, thank you!
B. Siobahn
- Posts
- 1233
- Registration date
- Thursday July 24, 2014
- Status
Hello Siobahn,
I've just tested the code again in the sample that you supplied in your opening post and all works as it should.
I am at a loss as to why you are having these niggling problems.
Perhaps upload your actual file (desensitised) and I'll test it in that instead to see what happens.
Cheerio,
vcoolio.
P.S. Sorry Siobahn, I missed the last part of your last post. Yes, it can be done but I'll wait until you upload your actual file. Just curious to see what is going on.
I've just tested the code again in the sample that you supplied in your opening post and all works as it should.
I am at a loss as to why you are having these niggling problems.
Perhaps upload your actual file (desensitised) and I'll test it in that instead to see what happens.
Cheerio,
vcoolio.
P.S. Sorry Siobahn, I missed the last part of your last post. Yes, it can be done but I'll wait until you upload your actual file. Just curious to see what is going on.
- Posts
- 1233
- Registration date
- Thursday July 24, 2014
- Status
Ahh Siobahn! Just picked up a typo in the code in post #10.
Line 15 should read:-
and line 20 should read:-
The number 1 in Cells(i, 1) represents Column A whereas in post #10 I've typed in Cells(i, 2) which represents Column B. My test worked fine because my personal sample has the number 1 in it!
That should fix that. Sorry for the oversight. Test it again (and also leave the >=70 part as is) then let me know.
Cheerio,
vcoolio.
Line 15 should read:-
Range(Cells(i, 1), Cells(i, 28)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
and line 20 should read:-
Range(Cells(i, 1), Cells(i, 28)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
The number 1 in Cells(i, 1) represents Column A whereas in post #10 I've typed in Cells(i, 2) which represents Column B. My test worked fine because my personal sample has the number 1 in it!
That should fix that. Sorry for the oversight. Test it again (and also leave the >=70 part as is) then let me know.
Cheerio,
vcoolio.
- Posts
- 2
- Registration date
- Thursday July 6, 2017
- Last seen
- July 7, 2017
-Thank you so so much! It works perfectly!! I truly appreciate it!
The "not equal to" SPEC or SALES CENTER code does not seem to be registering. Everything at 90% or 100% is turning green and ANYTHING without an answer in the keys field is turning yellow.
I only want those conditions to apply to homes that are SOLD (meaning the buyer column does NOT say "Spec" of "Sales Center". I am using Microsoft Excel 2007 - could this be the issue?