Move specific cells to New Sheet based on Value

[smartbuyer]

Hi Guys,

I have Sheet 2 which will have information

I would like to create a program that will do the following when button is clicked:

1. Find cells with value = "Pend" in "Sheet2" Columns: "B", "D" & "F"
2. Copy cells from "Sheet2" Columns: ("A" + "B"), o ("A"+"D") or ("A"+"F") if "Pend"
3. Create new sheet "Pending Accounts" Paste starting from Row 2
4. Change Cells in "Pending Accounts" from "Pend" to
"Debts" if from (Sheet2, Column "F")
"Bank" if from (Sheet2, Column "B")
"Loan" if from (Sheet2, Column "D")

I have attached an excel with how I want things to look like.
I have used the code in this thread: http://www.excelforum.com/excel-prog...ell-value.html

to copy paste to new sheet but I couldn't work out the other stuff I wanted done.

Here's the code I used to copy paste:

Please Login or Register  to view this content.

[Naveed Raza]

Hi,

As per my understanding if Column B,D & F changed to Pend then only copy that row to Pending Accounts sheet.
or else
If column B changed to Pend then that particular A & B row should copy right
If column D changed to Pend then that particular A & D row should copy right
If column F changed to Pend then that particular A & F row should copy right

[smartbuyer]

Hi Naveed.

Thanks for your response.

Sheet 2 will be updated manually as "Done" or "Pend."
What I wanted to do is to extract all the properties with "Pend" status into a new sheet but adding them as separate items showing: "Account Name" + Account Type (Bank, Loan, Debts) + Notes

-----------------------------------------
IF Columns B, D or F shows "Pend"
Then copy to "Pending Accounts" as Separate items that is:
Column "A" + Column "B" + Notes column
or
Column "A" + Column "D" + Notes column
or
Column "A" + Column "F" + Notes column

-----------------------------------------
Every time a Property shows "Pend" program will copy Property Name + "Pend" + Notes column
then program will change the word "Pend" to "Debts", "Bank" or "Loan"

"Debts" if Column F shows the word "Pend"
"Bank" if Column B shows the word "Pend"
"Loan" if Column D shows the word "Pend"
-----------------------------------------
The Pending Property Sheet will be created. I just illustrated the results I would like to see in the attachment.

[smartbuyer]

Found this too. Need help tweaking it so it does what Im looking for which is:

What I wanted to do is to extract all the properties with "Pend" status into a new sheet ("Pending Properties") but adding them as separate items showing:
"Account Name" + Account Type (Bank, Loan, Debts) + Notes = from Sheet2
Sheet 2 will be updated manually as "Done" or "Pend."

-----------------------------------------------

Please Login or Register  to view this content.

[arlu1201]

smartbuyer,

Welcome to the forum.

I have added code tags to your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[smartbuyer]

Noted. Thanks arlu1201

[arlu1201]

Try this code

Please Login or Register  to view this content.

[smartbuyer]

That is PERFECT! Thanks a bunch arlu1201!