Copy a row from one worksheet to another if a Cell value equals Text

[Pazzajunist]

Hi There,

Can anyone help me out here I'm just getting started with formulas in Excel. I have an Equipment register containing particular information but this equipment gets moved around.

I would like to know how I can get an entire row to copy into another worksheet if I enter the destination into the last column.

Something like: IF B2 = Melbourne - copy row 3 to worksheet named Melbourne.

 

[My Aswer Is This]

Several questions here:
1. Do you want this done immediately or do this when we click a button
I do not believe this can be done with just a formula.
Are you willing to use Vba?

We could write a script to do this when you click a button or have it happen when you enter a certain value into a certain cell on each row.
And when you copy the row to the sheet do you want to keep the existing row or delete it from the Master sheet.

Give us exact specifics like Master sheet name
And in what cell on the master sheet will the sheet name be entered into. Would it be column "B" on each row.

 

[Pazzajunist]

Hi
Thanks for the response. There are 5 master sheets separated into equipment for particular jobs. The first one being (RT Equipment). In the Last Column which is "O2" if I type in Melbourne I would like a copy of that entire row to transfer over to the sheet named Melbourne but also to remain in the Master Sheet.

Column O is where the location will be entered into always.

I am willing to add in VBA codes but I am very new to this and don't really know how to get them to work yet.

Thanks for your help in advance.

 

[My Aswer Is This]

Not sure what you mean when you say you have 5 Master Sheets.
You can only have one sheet named "Master"
So on the sheet named "Master" when you enter a sheet name in Column "O" you want that row copied to that sheet.

So if you enter "George" into column "O" you want that row copied to sheet named "George"
Or do I not understand.

So this script would only runs when you enter a value in column "O" of the sheet named "Master"

If this is what you want let me know. If not please explain more.

 

[Pazzajunist]

There are 5 sheets in this workbook that I would like this rule to apply to but I will use the first on for the example and work from there. The first Sheet is Called RT Equipment.

So what you have mentioned above will be correct.

 

[My Aswer Is This]

Put this script in your sheet where you plan to enter sheet names into column "O"
Now when you double click on the sheet name in column "O" this row will be copied to the sheet name you double clicked on.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 15 Then
Cancel = True
On Error GoTo M
Rows(Target.Row).Copy Sheets(Target.Value).Rows(Sheets(Target.Value).Cells(Rows.Count, "O").End(xlUp).Row + 1)
End If
Exit Sub
M:
MsgBox "No such sheet as " & Target.Value & " exist"
End Sub

 

[Pazzajunist]

Thanks for your help.

Column O is a location for a certain piece of equipment. Now the problem I have is that if I change the Location and double click again it copies it to the new location sheet but does not remove it from the first one, therefore it appears to be in two locations.

Can you help with this or do i have to physically remove it from the other list.

 

[My Aswer Is This]

Is there some sort of identifying information on that row we could search for.

For example we could have you double click on column "N"
the script would look in column "O" for the sheet name
And then search that sheet for a piece of unique information like a order number and then delete the row with that order number. We would need to know where that order number in located on the row.
Like if the order number was in column "K"
We search for that order number in column "K"


We would do this before you move the row to a new sheet.