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

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

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

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

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

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

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

You're checking for upper case:

Select Case UCase(Target.Value)

But this will never match any upper case string:
Case Is = "Erro1"

You could use this:
Select Case UCase(Target.Value)
Case Is = "ERRO1" '<-- you left out an R!!!

or

Select Case UCase(Target.Value)
Case Is = ucase("Erro1")

But watch your spelling, too.

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


--

Dave Peterson

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

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

Hi,

I changed your code for the userform button. When you click it the data are
now written to their respective sheets

Private Sub ENTERANDSAVE_Click()
ActiveWorkbook.Sheets("TRANSFER-ERRORS").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Boxname.Value
ActiveCell.Offset(0, 1) = Boxdate.Value
ActiveCell.Offset(0, 2) = Boxsup.Value
ActiveCell.Offset(0, 3) = Boxlead.Value
ActiveCell.Offset(0, 4) = Boxmanager.Value
ActiveCell.Offset(0, 5) = BoxSub.Value
ActiveCell.Offset(0, 6) = ERRORLIST.Value
'*************
'New code
Dim Myrange As Range
Select Case UCase(ERRORLIST.Value)
Case Is = "NO NOTES IN CSI"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "NO XFR CODE"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "WRONG XFR TYPE"
Set Myrange = ActiveCell.Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(ERRORLIST.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(ERRORLIST.Value).Range("A" & lastrow + 1)
End If
End Sub

Mike

"OfficeMan" wrote:

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

Ummm... after I input info to columns the system would not copy the row to
its sheet ....

double clicking works.. but I was wondering if it can be done automatically..

Thanks again.

"Mike H" wrote:

Hi,

I changed your code for the userform button. When you click it the data are
now written to their respective sheets

Private Sub ENTERANDSAVE_Click()
ActiveWorkbook.Sheets("TRANSFER-ERRORS").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Boxname.Value
ActiveCell.Offset(0, 1) = Boxdate.Value
ActiveCell.Offset(0, 2) = Boxsup.Value
ActiveCell.Offset(0, 3) = Boxlead.Value
ActiveCell.Offset(0, 4) = Boxmanager.Value
ActiveCell.Offset(0, 5) = BoxSub.Value
ActiveCell.Offset(0, 6) = ERRORLIST.Value
'*************
'New code
Dim Myrange As Range
Select Case UCase(ERRORLIST.Value)
Case Is = "NO NOTES IN CSI"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "NO XFR CODE"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "WRONG XFR TYPE"
Set Myrange = ActiveCell.Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(ERRORLIST.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(ERRORLIST.Value).Range("A" & lastrow + 1)
End If
End Sub

Mike

"OfficeMan" wrote:

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

You need to paste the revised code from my last post into the button on your
userform and then delete the double click event code

"OfficeMan" wrote:

Ummm... after I input info to columns the system would not copy the row to
its sheet ....

double clicking works.. but I was wondering if it can be done automatically..

Thanks again.

"Mike H" wrote:

Hi,

I changed your code for the userform button. When you click it the data are
now written to their respective sheets

Private Sub ENTERANDSAVE_Click()
ActiveWorkbook.Sheets("TRANSFER-ERRORS").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Boxname.Value
ActiveCell.Offset(0, 1) = Boxdate.Value
ActiveCell.Offset(0, 2) = Boxsup.Value
ActiveCell.Offset(0, 3) = Boxlead.Value
ActiveCell.Offset(0, 4) = Boxmanager.Value
ActiveCell.Offset(0, 5) = BoxSub.Value
ActiveCell.Offset(0, 6) = ERRORLIST.Value
'*************
'New code
Dim Myrange As Range
Select Case UCase(ERRORLIST.Value)
Case Is = "NO NOTES IN CSI"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "NO XFR CODE"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "WRONG XFR TYPE"
Set Myrange = ActiveCell.Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(ERRORLIST.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(ERRORLIST.Value).Range("A" & lastrow + 1)
End If
End Sub

Mike

"OfficeMan" wrote:

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

Done... however now I get an error "Compile Error" and it highligts this line
Select Case UCase(ERRORLIST.Value)
on the Main sheet code.

The form code no errors.


"Mike H" wrote:

You need to paste the revised code from my last post into the button on your
userform and then delete the double click event code

"OfficeMan" wrote:

Ummm... after I input info to columns the system would not copy the row to
its sheet ....

double clicking works.. but I was wondering if it can be done automatically..

Thanks again.

"Mike H" wrote:

Hi,

I changed your code for the userform button. When you click it the data are
now written to their respective sheets

Private Sub ENTERANDSAVE_Click()
ActiveWorkbook.Sheets("TRANSFER-ERRORS").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Boxname.Value
ActiveCell.Offset(0, 1) = Boxdate.Value
ActiveCell.Offset(0, 2) = Boxsup.Value
ActiveCell.Offset(0, 3) = Boxlead.Value
ActiveCell.Offset(0, 4) = Boxmanager.Value
ActiveCell.Offset(0, 5) = BoxSub.Value
ActiveCell.Offset(0, 6) = ERRORLIST.Value
'*************
'New code
Dim Myrange As Range
Select Case UCase(ERRORLIST.Value)
Case Is = "NO NOTES IN CSI"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "NO XFR CODE"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "WRONG XFR TYPE"
Set Myrange = ActiveCell.Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(ERRORLIST.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(ERRORLIST.Value).Range("A" & lastrow + 1)
End If
End Sub

Mike

"OfficeMan" wrote:

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

Try wrapping the code in a Worksheet_Change event.


Gord Dibben MS Excel MVP

On Thu, 16 Oct 2008 09:54:01 -0700, OfficeMan
wrote:

Ummm... after I input info to columns the system would not copy the row to
its sheet ....

double clicking works.. but I was wondering if it can be done automatically..

Thanks again.

"Mike H" wrote:

Hi,

I changed your code for the userform button. When you click it the data are
now written to their respective sheets

Private Sub ENTERANDSAVE_Click()
ActiveWorkbook.Sheets("TRANSFER-ERRORS").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Boxname.Value
ActiveCell.Offset(0, 1) = Boxdate.Value
ActiveCell.Offset(0, 2) = Boxsup.Value
ActiveCell.Offset(0, 3) = Boxlead.Value
ActiveCell.Offset(0, 4) = Boxmanager.Value
ActiveCell.Offset(0, 5) = BoxSub.Value
ActiveCell.Offset(0, 6) = ERRORLIST.Value
'*************
'New code
Dim Myrange As Range
Select Case UCase(ERRORLIST.Value)
Case Is = "NO NOTES IN CSI"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "NO XFR CODE"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "WRONG XFR TYPE"
Set Myrange = ActiveCell.Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(ERRORLIST.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(ERRORLIST.Value).Range("A" & lastrow + 1)
End If
End Sub

Mike

"OfficeMan" wrote:

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

hI,

I've uploaded you file again with complete code for all the options in
select case

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

Mike

"OfficeMan" wrote:

Done... however now I get an error "Compile Error" and it highligts this line
Select Case UCase(ERRORLIST.Value)
on the Main sheet code.

The form code no errors.


"Mike H" wrote:

You need to paste the revised code from my last post into the button on your
userform and then delete the double click event code

"OfficeMan" wrote:

Ummm... after I input info to columns the system would not copy the row to
its sheet ....

double clicking works.. but I was wondering if it can be done automatically..

Thanks again.

"Mike H" wrote:

Hi,

I changed your code for the userform button. When you click it the data are
now written to their respective sheets

Private Sub ENTERANDSAVE_Click()
ActiveWorkbook.Sheets("TRANSFER-ERRORS").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Boxname.Value
ActiveCell.Offset(0, 1) = Boxdate.Value
ActiveCell.Offset(0, 2) = Boxsup.Value
ActiveCell.Offset(0, 3) = Boxlead.Value
ActiveCell.Offset(0, 4) = Boxmanager.Value
ActiveCell.Offset(0, 5) = BoxSub.Value
ActiveCell.Offset(0, 6) = ERRORLIST.Value
'*************
'New code
Dim Myrange As Range
Select Case UCase(ERRORLIST.Value)
Case Is = "NO NOTES IN CSI"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "NO XFR CODE"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "WRONG XFR TYPE"
Set Myrange = ActiveCell.Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(ERRORLIST.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(ERRORLIST.Value).Range("A" & lastrow + 1)
End If
End Sub

Mike

"OfficeMan" wrote:

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

Great! - It functions!

Thank you very much.



"Mike H" wrote:

hI,

I've uploaded you file again with complete code for all the options in
select case

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

Mike

"OfficeMan" wrote:

Done... however now I get an error "Compile Error" and it highligts this line
Select Case UCase(ERRORLIST.Value)
on the Main sheet code.

The form code no errors.


"Mike H" wrote:

You need to paste the revised code from my last post into the button on your
userform and then delete the double click event code

"OfficeMan" wrote:

Ummm... after I input info to columns the system would not copy the row to
its sheet ....

double clicking works.. but I was wondering if it can be done automatically..

Thanks again.

"Mike H" wrote:

Hi,

I changed your code for the userform button. When you click it the data are
now written to their respective sheets

Private Sub ENTERANDSAVE_Click()
ActiveWorkbook.Sheets("TRANSFER-ERRORS").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Boxname.Value
ActiveCell.Offset(0, 1) = Boxdate.Value
ActiveCell.Offset(0, 2) = Boxsup.Value
ActiveCell.Offset(0, 3) = Boxlead.Value
ActiveCell.Offset(0, 4) = Boxmanager.Value
ActiveCell.Offset(0, 5) = BoxSub.Value
ActiveCell.Offset(0, 6) = ERRORLIST.Value
'*************
'New code
Dim Myrange As Range
Select Case UCase(ERRORLIST.Value)
Case Is = "NO NOTES IN CSI"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "NO XFR CODE"
Set Myrange = ActiveCell.Resize(1, 7)
Case Is = "WRONG XFR TYPE"
Set Myrange = ActiveCell.Resize(1, 7)
End Select
If Not Myrange Is Nothing Then
lastrow = Sheets(ERRORLIST.Value).Cells(Rows.Count, "A").End(xlUp).Row
Myrange.Copy Destination:=Sheets(ERRORLIST.Value).Range("A" & lastrow + 1)
End If
End Sub

Mike

"OfficeMan" wrote:

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