Copy data from one worksheet to another using spec criteria [Solved]
Siobahn2
Posts Thursday July 6, 2017Registration date
July 7, 2017
Last seen
-
Jul 6, 2017 at 02:52 PM - Latest reply: vcoolio1169
Posts Thursday July 24, 2014Registration dateModeratorStatus
June 10, 2018
Last seen
- Jul 29, 2017 at 07:04 AM
I have tried to "customize" other answers similar to this question to fit what I need but I am new to the whole macro thing. I have only used simple Excel formulas, so I need help!
This is what I would like to do:
I have a Status report that lists all of the homes we currently have under construction.
1. When a SOLD home (one that has a buyers name, not a sales center or spec) moves to 45%, I want to automatically copy that entire row from one worksheet to another (not removed from the master sheet, just copied to a separate 45% sheet - let's say sheet2)
2. If a home is sold and at 70% I would like two actions performed: I want this row copied, like 45%, to another sheet (sheet3). 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).
3. If a home is sold and at 90% or 100% I want it to be highlighted green on the main sheet (sheet1).
Like I said, I have found people wanting to do similar things, and I've tried to tweak the code to fit what I need, but I cannot seem to get all of my conditions met.
Any help you can give me would be greatly appreciated!
I've created a sample of what I am working with, just for you to test code.
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:-
Sorry to bother you again, after using this fully for the first time.. I have notice that everything works except for the ones regarding homes that are SOLD.
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?
Alright, I screwed something up. I removed a column I no longer needed and now the code is showing a runtime error. Also, I understand you said to remove the conditional formatting... can I still use regular formatting throughout the spreadsheet though?
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.
- 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:-
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
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.
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.
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.
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.
has helped 1869 users this month
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?