Thanks a lot... really appreciate it.

Question... would it be too difficult to make the row copy itself?

I mean the moment I enter the error name on column G it copies to the sheet
automatically? Instead of double clicking?

thanks


"Mike H" wrote:

Hi,

Your file is back and fixed
http://www.savefile.com/files/1842462
Nice coding on that userform by the way

Now then the fix is in the book and the code below. We are doing text
comparisons and its far to error prone to rely on typing in the correct case
so note that below i've changed the sheet names to upper case. Note this
doesn't mean you have to chnage anything in your dropdown. But when you add
extra lines to select case you enter then in upper case

Select Case UCase(Target.Value)
Case Is = "ANOTHER REASON

Mike

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim Myrange As Range
If Target.Column < 7 Then Exit Sub
Select Case UCase(Target.Value)
Case Is = "NO NOTES IN CSI"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
Case Is = "N0 XFR CODE"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
Case Is = "WRONG XFR TYPE"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(Target.Value).Range("A" & lastrow + 1)
End If
End Sub

"OfficeMan" wrote:

Here it is. Thank again.

http://www.savefile.com/files/1842451

"Mike H" wrote:

Upload your workbook here and post the link

http://www.savefile.com/

Miike

"OfficeMan" wrote:

Thanks again, this time there is no errors, However the row do not copy.

I have a form created on the main sheet, the form will fill in every column
however once the G colum gets the input of either Error2 or Error3 I want
that row to copy to the sheet corresponding to it, named either Error2 or
Error 3.
I have the sheets named as the inputs. I have this so far.

Note. My input rows start on row # 6 maybe that helps. Thanks again.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim Myrange As Range
If Target.Column < 7 Then Exit Sub
Select Case UCase(Target.Value)
Case Is = "Erro1"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
Case Is = "Error2"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
Case Is = "Error3"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.copy Destination:=Sheets(Target.Value).Range("A" & lastrow + 1)

End If
End Sub

"Mike H" wrote:

That was caused by my sloppy code. Try this instead.

The error you enter into column G must be the same as a sheet name. for
example if you enter Error 3 there must be a sheet of that name and note that
Error 3 is not the same as Error3 with no space. Upper or lower case doesn't
matter.

If you want to add more errors then simply add more lines in the select case
statement
Case Is = "ERROR 999"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)

but as noted before the must be a sheet called Error 999


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim Myrange As Range
If Target.Column < 7 Then Exit Sub
Select Case UCase(Target.Value)
Case Is = "ERROR 1"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
Case Is = "ERROR 2"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
Case Is = "ERROR 3"
Set Myrange = Target.Offset(0, -6).Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(Target.Value).Range("A" & lastrow + 1)
'myrange.ClearContents
End If
End Sub

Mike
"OfficeMan" wrote:

Thanks for the help!

I pasted this and it gives me an error of "Object Required" and it
highlights this line

If Not Myrange Is Nothing Then

Is there a way to have the row coppied to the sheet it corresponds
automatically in other words, if column G receives an ipunt of ERROR2 that
would triger the row to be copied?

I do not need the row to be deleted so I deleted the commented outline -

thanks again.



"Mike H" wrote:

Hi,

I've assigned this to the Double Click event. You enter data into your sheet
then double click the cell in column g and the data are copied to their
respective sheets.

Right click the sheet tab on 'Transfers' view code and paste this in. Note
there'e a commented out line that will clear the data from the maion sheet if
that's what you want.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column < 7 Then Exit Sub
Select Case UCase(Target.Value)
Case Is = "ERROR 1"
Set myrange = Target.Offset(0, -6).Resize(1, 7)
Case Is = "ERROR 2"
Set myrange = Target.Offset(0, -6).Resize(1, 7)
Case Is = "ERROR 3"
Set myrange = Target.Offset(0, -6).Resize(1, 7)
End Select
If Not myrange Is Nothing Then
lastrow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row
myrange.Copy Destination:=Sheets(Target.Value).Range("A" & lastrow + 1)
'myrange.ClearContents
End If
End Sub

Mike

"OfficeMan" wrote:

Good Morning!

I need some help.

I have this workbook with a total of 8 sheets.

The main sheet is called €œTRANSFERS€�
The other 8 are the named €œERROR1€� €œERROR2€�€�ERROR3€� and so on€¦

On the main sheet I have this columns

A B C D E F G
Name Date Sup Lead Manager Number Error

What I need is each time a row in filled on the main sheet; I would like it
to be copied to each sheet according to the error.

The error will be entered on ROW G whether is an error1 or error 2, I want
the row to remain on the MAIN sheet but a copy to be moved to each sheet
according to the error.

Can this be possible? I have been trying but just can make Macros work.

Any help would be appreciated. Thank you