Copy data from one worksheet to another using spec criteria Solved/Closed

Question

Hello,

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.

Thank you!

https://www.dropbox.com/s/aagd2w09c6f6bmj/Status%20Macro%20Tester.xlsx?dl=0

vcoolio · Answer

Hello Siobahn, 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.

vcoolio · Answer

Hello Siobahn,

You're welcome. Glad that I was able to help.

Cheerio,
vcoolio.

vcoolio · Answer

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.

vcoolio · Answer

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:- 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,

vcoolio · Answer

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:- 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.

vcoolio · Answer

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.

Copy data from one worksheet to another using spec criteria

6 responses

Similar discussions

Subscribe To Our Newsletter!