Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
2
Registration date
Thursday July 6, 2017
Status
Member
Last seen
July 7, 2017
 -
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 -
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
See more

6 replies

Answer 1 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
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.
Thank you
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2887 users have said thank you to us this month

Report
Posts
2
Registration date
Thursday July 6, 2017
Status
Member
Last seen
July 7, 2017
Vcoolio,

Thank you so so much! It works perfectly!! I truly appreciate it!
Report

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?
Answer 2 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
Hello Siobahn,

You're welcome. Glad that I was able to help.

Cheerio,
vcoolio.
Report

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?
Answer 3 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
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.
Answer 4 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
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,
Report

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: 

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.
Answer 5 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
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.
Report

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
Answer 6 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
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.
Report
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
Ahh Siobahn! Just picked up a typo in the code in post #10.

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.
Ask a question