Hi, I posted a question here but recieved no responses so I shall re-phrase
the question in the hope that someone will take the bait.

Sheet 1. When a specific value (CH) is selected from a drop down list (in
column M), I would like to have all the data from cells on the same row (A-K)
copied to the next empty row in Sheet 2.
So, the theory is that data is entered by the user in cells A-M. When the
option 'CH' is selected from the list of options in cell M, A-K is copied to
Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
Can someone advise if this is achievable or do I have to try a different
approach?
Thanks.
--
Traa Dy Liooar

Jock

there are two way of doing theis. First to use a worksheet change function
when the drop down box is changed to move the data. but this is not really a
great way of doing this because if the wrong value is selected unwanted data
will be copied. the second method is to use a Button to perform the
operation after the value is selected which willreduce the number of wrong
items theat will be moved.


1) Is the Drop down List a Data validation or an Autofilter?

2) What cell is the Drop down list Located?


"Jock" wrote:

> Hi, I posted a question here but recieved no responses so I shall re-phrase
> the question in the hope that someone will take the bait.
>
> Sheet 1. When a specific value (CH) is selected from a drop down list (in
> column M), I would like to have all the data from cells on the same row (A-K)
> copied to the next empty row in Sheet 2.
> So, the theory is that data is entered by the user in cells A-M. When the
> option 'CH' is selected from the list of options in cell M, A-K is copied to
> Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> Can someone advise if this is achievable or do I have to try a different
> approach?
> Thanks.
> --
> Traa Dy Liooar
>
> Jock

Hi Joel,
Good thinking about the button to reduce wrong entries. The drop down list
is a Data Validation and is located in each cell in column 'M' on Sheet 1.

Cheers,
--
Traa Dy Liooar

Jock


"Joel" wrote:

> there are two way of doing theis. First to use a worksheet change function
> when the drop down box is changed to move the data. but this is not really a
> great way of doing this because if the wrong value is selected unwanted data
> will be copied. the second method is to use a Button to perform the
> operation after the value is selected which willreduce the number of wrong
> items theat will be moved.
>
>
> 1) Is the Drop down List a Data validation or an Autofilter?
>
> 2) What cell is the Drop down list Located?
>
>
> "Jock" wrote:
>
> > Hi, I posted a question here but recieved no responses so I shall re-phrase
> > the question in the hope that someone will take the bait.
> >
> > Sheet 1. When a specific value (CH) is selected from a drop down list (in
> > column M), I would like to have all the data from cells on the same row (A-K)
> > copied to the next empty row in Sheet 2.
> > So, the theory is that data is entered by the user in cells A-M. When the
> > option 'CH' is selected from the list of options in cell M, A-K is copied to
> > Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> > Can someone advise if this is achievable or do I have to try a different
> > approach?
> > Thanks.
> > --
> > Traa Dy Liooar
> >
> > Jock

Private Sub CommandButton1_Click()

If ActiveCell.Value = "CH" Then
RowNo = ActiveCell.Row
With ActiveSheet
Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
End With
With Sheets("Sheet2")
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = Lastrow + 1
CopyRange.Copy Destination:=.Range("A" & NewRow)
End With
End If

End Sub

"Jock" wrote:

> Hi Joel,
> Good thinking about the button to reduce wrong entries. The drop down list
> is a Data Validation and is located in each cell in column 'M' on Sheet 1.
>
> Cheers,
> --
> Traa Dy Liooar
>
> Jock
>
>
> "Joel" wrote:
>
> > there are two way of doing theis. First to use a worksheet change function
> > when the drop down box is changed to move the data. but this is not really a
> > great way of doing this because if the wrong value is selected unwanted data
> > will be copied. the second method is to use a Button to perform the
> > operation after the value is selected which willreduce the number of wrong
> > items theat will be moved.
> >
> >
> > 1) Is the Drop down List a Data validation or an Autofilter?
> >
> > 2) What cell is the Drop down list Located?
> >
> >
> > "Jock" wrote:
> >
> > > Hi, I posted a question here but recieved no responses so I shall re-phrase
> > > the question in the hope that someone will take the bait.
> > >
> > > Sheet 1. When a specific value (CH) is selected from a drop down list (in
> > > column M), I would like to have all the data from cells on the same row (A-K)
> > > copied to the next empty row in Sheet 2.
> > > So, the theory is that data is entered by the user in cells A-M. When the
> > > option 'CH' is selected from the list of options in cell M, A-K is copied to
> > > Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> > > Can someone advise if this is achievable or do I have to try a different
> > > approach?
> > > Thanks.
> > > --
> > > Traa Dy Liooar
> > >
> > > Jock

That works a treat, thanks.
Is it possible to just paste values ie, no formatting?
Also can the user be taken to Sheet 2 when the button is clicked? That would
help a lot too! There's already a button there to return the user to the
active cell on Sheet 1.

Thanks
--
Traa Dy Liooar

Jock


"Joel" wrote:

> Private Sub CommandButton1_Click()
>
> If ActiveCell.Value = "CH" Then
> RowNo = ActiveCell.Row
> With ActiveSheet
> Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> End With
> With Sheets("Sheet2")
> Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> NewRow = Lastrow + 1
> CopyRange.Copy Destination:=.Range("A" & NewRow)
> End With
> End If
>
> End Sub
>
> "Jock" wrote:
>
> > Hi Joel,
> > Good thinking about the button to reduce wrong entries. The drop down list
> > is a Data Validation and is located in each cell in column 'M' on Sheet 1.
> >
> > Cheers,
> > --
> > Traa Dy Liooar
> >
> > Jock
> >
> >
> > "Joel" wrote:
> >
> > > there are two way of doing theis. First to use a worksheet change function
> > > when the drop down box is changed to move the data. but this is not really a
> > > great way of doing this because if the wrong value is selected unwanted data
> > > will be copied. the second method is to use a Button to perform the
> > > operation after the value is selected which willreduce the number of wrong
> > > items theat will be moved.
> > >
> > >
> > > 1) Is the Drop down List a Data validation or an Autofilter?
> > >
> > > 2) What cell is the Drop down list Located?
> > >
> > >
> > > "Jock" wrote:
> > >
> > > > Hi, I posted a question here but recieved no responses so I shall re-phrase
> > > > the question in the hope that someone will take the bait.
> > > >
> > > > Sheet 1. When a specific value (CH) is selected from a drop down list (in
> > > > column M), I would like to have all the data from cells on the same row (A-K)
> > > > copied to the next empty row in Sheet 2.
> > > > So, the theory is that data is entered by the user in cells A-M. When the
> > > > option 'CH' is selected from the list of options in cell M, A-K is copied to
> > > > Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> > > > Can someone advise if this is achievable or do I have to try a different
> > > > approach?
> > > > Thanks.
> > > > --
> > > > Traa Dy Liooar
> > > >
> > > > Jock

Use pastespecial to pate values only

Private Sub CommandButton1_Click()

If ActiveCell.Value = "CH" Then
RowNo = ActiveCell.Row
With ActiveSheet
Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
End With
With Sheets("Sheet2")
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = Lastrow + 1
CopyRange.Copy
.Range("A" & NewRow).pastespecial paste:=xlPasteValues
..activate
End With
End If

End Sub



"Jock" wrote:

> That works a treat, thanks.
> Is it possible to just paste values ie, no formatting?
> Also can the user be taken to Sheet 2 when the button is clicked? That would
> help a lot too! There's already a button there to return the user to the
> active cell on Sheet 1.
>
> Thanks
> --
> Traa Dy Liooar
>
> Jock
>
>
> "Joel" wrote:
>
> > Private Sub CommandButton1_Click()
> >
> > If ActiveCell.Value = "CH" Then
> > RowNo = ActiveCell.Row
> > With ActiveSheet
> > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > End With
> > With Sheets("Sheet2")
> > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > NewRow = Lastrow + 1
> > CopyRange.Copy Destination:=.Range("A" & NewRow)
> > End With
> > End If
> >
> > End Sub
> >
> > "Jock" wrote:
> >
> > > Hi Joel,
> > > Good thinking about the button to reduce wrong entries. The drop down list
> > > is a Data Validation and is located in each cell in column 'M' on Sheet 1.
> > >
> > > Cheers,
> > > --
> > > Traa Dy Liooar
> > >
> > > Jock
> > >
> > >
> > > "Joel" wrote:
> > >
> > > > there are two way of doing theis. First to use a worksheet change function
> > > > when the drop down box is changed to move the data. but this is not really a
> > > > great way of doing this because if the wrong value is selected unwanted data
> > > > will be copied. the second method is to use a Button to perform the
> > > > operation after the value is selected which willreduce the number of wrong
> > > > items theat will be moved.
> > > >
> > > >
> > > > 1) Is the Drop down List a Data validation or an Autofilter?
> > > >
> > > > 2) What cell is the Drop down list Located?
> > > >
> > > >
> > > > "Jock" wrote:
> > > >
> > > > > Hi, I posted a question here but recieved no responses so I shall re-phrase
> > > > > the question in the hope that someone will take the bait.
> > > > >
> > > > > Sheet 1. When a specific value (CH) is selected from a drop down list (in
> > > > > column M), I would like to have all the data from cells on the same row (A-K)
> > > > > copied to the next empty row in Sheet 2.
> > > > > So, the theory is that data is entered by the user in cells A-M. When the
> > > > > option 'CH' is selected from the list of options in cell M, A-K is copied to
> > > > > Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> > > > > Can someone advise if this is achievable or do I have to try a different
> > > > > approach?
> > > > > Thanks.
> > > > > --
> > > > > Traa Dy Liooar
> > > > >
> > > > > Jock

Nice one.
Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only
That would help my cause!

Thanks
--
Traa Dy Liooar

Jock


"Joel" wrote:

> Use pastespecial to pate values only
>
> Private Sub CommandButton1_Click()
>
> If ActiveCell.Value = "CH" Then
> RowNo = ActiveCell.Row
> With ActiveSheet
> Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> End With
> With Sheets("Sheet2")
> Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> NewRow = Lastrow + 1
> CopyRange.Copy
> .Range("A" & NewRow).pastespecial paste:=xlPasteValues
> .activate
> End With
> End If
>
> End Sub
>
>
>
> "Jock" wrote:
>
> > That works a treat, thanks.
> > Is it possible to just paste values ie, no formatting?
> > Also can the user be taken to Sheet 2 when the button is clicked? That would
> > help a lot too! There's already a button there to return the user to the
> > active cell on Sheet 1.
> >
> > Thanks
> > --
> > Traa Dy Liooar
> >
> > Jock
> >
> >
> > "Joel" wrote:
> >
> > > Private Sub CommandButton1_Click()
> > >
> > > If ActiveCell.Value = "CH" Then
> > > RowNo = ActiveCell.Row
> > > With ActiveSheet
> > > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > > End With
> > > With Sheets("Sheet2")
> > > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > > NewRow = Lastrow + 1
> > > CopyRange.Copy Destination:=.Range("A" & NewRow)
> > > End With
> > > End If
> > >
> > > End Sub
> > >
> > > "Jock" wrote:
> > >
> > > > Hi Joel,
> > > > Good thinking about the button to reduce wrong entries. The drop down list
> > > > is a Data Validation and is located in each cell in column 'M' on Sheet 1.
> > > >
> > > > Cheers,
> > > > --
> > > > Traa Dy Liooar
> > > >
> > > > Jock
> > > >
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > there are two way of doing theis. First to use a worksheet change function
> > > > > when the drop down box is changed to move the data. but this is not really a
> > > > > great way of doing this because if the wrong value is selected unwanted data
> > > > > will be copied. the second method is to use a Button to perform the
> > > > > operation after the value is selected which willreduce the number of wrong
> > > > > items theat will be moved.
> > > > >
> > > > >
> > > > > 1) Is the Drop down List a Data validation or an Autofilter?
> > > > >
> > > > > 2) What cell is the Drop down list Located?
> > > > >
> > > > >
> > > > > "Jock" wrote:
> > > > >
> > > > > > Hi, I posted a question here but recieved no responses so I shall re-phrase
> > > > > > the question in the hope that someone will take the bait.
> > > > > >
> > > > > > Sheet 1. When a specific value (CH) is selected from a drop down list (in
> > > > > > column M), I would like to have all the data from cells on the same row (A-K)
> > > > > > copied to the next empty row in Sheet 2.
> > > > > > So, the theory is that data is entered by the user in cells A-M. When the
> > > > > > option 'CH' is selected from the list of options in cell M, A-K is copied to
> > > > > > Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> > > > > > Can someone advise if this is achievable or do I have to try a different
> > > > > > approach?
> > > > > > Thanks.
> > > > > > --
> > > > > > Traa Dy Liooar
> > > > > >
> > > > > > Jock

Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B'

Private Sub CommandButton1_Click()

If ActiveCell.Value = "CH" Then
RowNo = ActiveCell.Row
With ActiveSheet
Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
End With
With Sheets("Sheet2")
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = Lastrow + 1
end with
Sheets("Sheet2").Range("A").value = .Range("B" & Newrow)
Sheets("Sheet2").Range("B").value = .Range("E" & Newrow)
Sheets("Sheet2").Range("C").value = .Range("F" & Newrow)
Sheets("Sheet2").Range("D").value = .Range("G" & Newrow)
Sheets("Sheet2").Range("E").value = .Range("H & Newrow)
Sheets("Sheet2").Range("F").value = .Range("K" & Newrow)
Sheets("Sheet2").Range("G").value = .Range("L" & Newrow)
.activate
End With
End If

End Sub

"Jock" wrote:

> Nice one.
> Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only
> That would help my cause!
>
> Thanks
> --
> Traa Dy Liooar
>
> Jock
>
>
> "Joel" wrote:
>
> > Use pastespecial to pate values only
> >
> > Private Sub CommandButton1_Click()
> >
> > If ActiveCell.Value = "CH" Then
> > RowNo = ActiveCell.Row
> > With ActiveSheet
> > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > End With
> > With Sheets("Sheet2")
> > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > NewRow = Lastrow + 1
> > CopyRange.Copy
> > .Range("A" & NewRow).pastespecial paste:=xlPasteValues
> > .activate
> > End With
> > End If
> >
> > End Sub
> >
> >
> >
> > "Jock" wrote:
> >
> > > That works a treat, thanks.
> > > Is it possible to just paste values ie, no formatting?
> > > Also can the user be taken to Sheet 2 when the button is clicked? That would
> > > help a lot too! There's already a button there to return the user to the
> > > active cell on Sheet 1.
> > >
> > > Thanks
> > > --
> > > Traa Dy Liooar
> > >
> > > Jock
> > >
> > >
> > > "Joel" wrote:
> > >
> > > > Private Sub CommandButton1_Click()
> > > >
> > > > If ActiveCell.Value = "CH" Then
> > > > RowNo = ActiveCell.Row
> > > > With ActiveSheet
> > > > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > > > End With
> > > > With Sheets("Sheet2")
> > > > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > > > NewRow = Lastrow + 1
> > > > CopyRange.Copy Destination:=.Range("A" & NewRow)
> > > > End With
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > > "Jock" wrote:
> > > >
> > > > > Hi Joel,
> > > > > Good thinking about the button to reduce wrong entries. The drop down list
> > > > > is a Data Validation and is located in each cell in column 'M' on Sheet 1.
> > > > >
> > > > > Cheers,
> > > > > --
> > > > > Traa Dy Liooar
> > > > >
> > > > > Jock
> > > > >
> > > > >
> > > > > "Joel" wrote:
> > > > >
> > > > > > there are two way of doing theis. First to use a worksheet change function
> > > > > > when the drop down box is changed to move the data. but this is not really a
> > > > > > great way of doing this because if the wrong value is selected unwanted data
> > > > > > will be copied. the second method is to use a Button to perform the
> > > > > > operation after the value is selected which willreduce the number of wrong
> > > > > > items theat will be moved.
> > > > > >
> > > > > >
> > > > > > 1) Is the Drop down List a Data validation or an Autofilter?
> > > > > >
> > > > > > 2) What cell is the Drop down list Located?
> > > > > >
> > > > > >
> > > > > > "Jock" wrote:
> > > > > >
> > > > > > > Hi, I posted a question here but recieved no responses so I shall re-phrase
> > > > > > > the question in the hope that someone will take the bait.
> > > > > > >
> > > > > > > Sheet 1. When a specific value (CH) is selected from a drop down list (in
> > > > > > > column M), I would like to have all the data from cells on the same row (A-K)
> > > > > > > copied to the next empty row in Sheet 2.
> > > > > > > So, the theory is that data is entered by the user in cells A-M. When the
> > > > > > > option 'CH' is selected from the list of options in cell M, A-K is copied to
> > > > > > > Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> > > > > > > Can someone advise if this is achievable or do I have to try a different
> > > > > > > approach?
> > > > > > > Thanks.
> > > > > > > --
> > > > > > > Traa Dy Liooar
> > > > > > >
> > > > > > > Jock

It's cell B also cells E to H and K to L inclusive (i.e. omitting A, C, D, I
and J)
Thanks for your help on this - much appreciated!
--
Traa Dy Liooar

Jock


"Joel" wrote:

> Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B'
>
> Private Sub CommandButton1_Click()
>
> If ActiveCell.Value = "CH" Then
> RowNo = ActiveCell.Row
> With ActiveSheet
> Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> End With
> With Sheets("Sheet2")
> Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> NewRow = Lastrow + 1
> end with
> Sheets("Sheet2").Range("A").value = .Range("B" & Newrow)
> Sheets("Sheet2").Range("B").value = .Range("E" & Newrow)
> Sheets("Sheet2").Range("C").value = .Range("F" & Newrow)
> Sheets("Sheet2").Range("D").value = .Range("G" & Newrow)
> Sheets("Sheet2").Range("E").value = .Range("H & Newrow)
> Sheets("Sheet2").Range("F").value = .Range("K" & Newrow)
> Sheets("Sheet2").Range("G").value = .Range("L" & Newrow)
> .activate
> End With
> End If
>
> End Sub
>
> "Jock" wrote:
>
> > Nice one.
> > Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only
> > That would help my cause!
> >
> > Thanks
> > --
> > Traa Dy Liooar
> >
> > Jock
> >
> >
> > "Joel" wrote:
> >
> > > Use pastespecial to pate values only
> > >
> > > Private Sub CommandButton1_Click()
> > >
> > > If ActiveCell.Value = "CH" Then
> > > RowNo = ActiveCell.Row
> > > With ActiveSheet
> > > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > > End With
> > > With Sheets("Sheet2")
> > > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > > NewRow = Lastrow + 1
> > > CopyRange.Copy
> > > .Range("A" & NewRow).pastespecial paste:=xlPasteValues
> > > .activate
> > > End With
> > > End If
> > >
> > > End Sub
> > >
> > >
> > >
> > > "Jock" wrote:
> > >
> > > > That works a treat, thanks.
> > > > Is it possible to just paste values ie, no formatting?
> > > > Also can the user be taken to Sheet 2 when the button is clicked? That would
> > > > help a lot too! There's already a button there to return the user to the
> > > > active cell on Sheet 1.
> > > >
> > > > Thanks
> > > > --
> > > > Traa Dy Liooar
> > > >
> > > > Jock
> > > >
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > Private Sub CommandButton1_Click()
> > > > >
> > > > > If ActiveCell.Value = "CH" Then
> > > > > RowNo = ActiveCell.Row
> > > > > With ActiveSheet
> > > > > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > > > > End With
> > > > > With Sheets("Sheet2")
> > > > > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > > > > NewRow = Lastrow + 1
> > > > > CopyRange.Copy Destination:=.Range("A" & NewRow)
> > > > > End With
> > > > > End If
> > > > >
> > > > > End Sub
> > > > >
> > > > > "Jock" wrote:
> > > > >
> > > > > > Hi Joel,
> > > > > > Good thinking about the button to reduce wrong entries. The drop down list
> > > > > > is a Data Validation and is located in each cell in column 'M' on Sheet 1.
> > > > > >
> > > > > > Cheers,
> > > > > > --
> > > > > > Traa Dy Liooar
> > > > > >
> > > > > > Jock
> > > > > >
> > > > > >
> > > > > > "Joel" wrote:
> > > > > >
> > > > > > > there are two way of doing theis. First to use a worksheet change function
> > > > > > > when the drop down box is changed to move the data. but this is not really a
> > > > > > > great way of doing this because if the wrong value is selected unwanted data
> > > > > > > will be copied. the second method is to use a Button to perform the
> > > > > > > operation after the value is selected which willreduce the number of wrong
> > > > > > > items theat will be moved.
> > > > > > >
> > > > > > >
> > > > > > > 1) Is the Drop down List a Data validation or an Autofilter?
> > > > > > >
> > > > > > > 2) What cell is the Drop down list Located?
> > > > > > >
> > > > > > >
> > > > > > > "Jock" wrote:
> > > > > > >
> > > > > > > > Hi, I posted a question here but recieved no responses so I shall re-phrase
> > > > > > > > the question in the hope that someone will take the bait.
> > > > > > > >
> > > > > > > > Sheet 1. When a specific value (CH) is selected from a drop down list (in
> > > > > > > > column M), I would like to have all the data from cells on the same row (A-K)
> > > > > > > > copied to the next empty row in Sheet 2.
> > > > > > > > So, the theory is that data is entered by the user in cells A-M. When the
> > > > > > > > option 'CH' is selected from the list of options in cell M, A-K is copied to
> > > > > > > > Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> > > > > > > > Can someone advise if this is achievable or do I have to try a different
> > > > > > > > approach?
> > > > > > > > Thanks.
> > > > > > > > --
> > > > > > > > Traa Dy Liooar
> > > > > > > >
> > > > > > > > Jock

Private Sub CommandButton1_Click()

If ActiveCell.Value = "CH" Then
RowNo = ActiveCell.Row
With ActiveSheet
Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
End With
With Sheets("Sheet2")
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = Lastrow + 1
end with
Sheets("Sheet2").Range("B").value = .Range("B" & Newrow)
Sheets("Sheet2").Range("E").value = .Range("E" & Newrow)
Sheets("Sheet2").Range("F").value = .Range("F" & Newrow)
Sheets("Sheet2").Range("G").value = .Range("G" & Newrow)
Sheets("Sheet2").Range("H").value = .Range("H & Newrow)
Sheets("Sheet2").Range("K").value = .Range("K" & Newrow)
Sheets("Sheet2").Range("L").value = .Range("L" & Newrow)
.activate
End With
End If

End Sub



"Jock" wrote:

> It's cell B also cells E to H and K to L inclusive (i.e. omitting A, C, D, I
> and J)
> Thanks for your help on this - much appreciated!
> --
> Traa Dy Liooar
>
> Jock
>
>
> "Joel" wrote:
>
> > Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B'
> >
> > Private Sub CommandButton1_Click()
> >
> > If ActiveCell.Value = "CH" Then
> > RowNo = ActiveCell.Row
> > With ActiveSheet
> > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > End With
> > With Sheets("Sheet2")
> > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > NewRow = Lastrow + 1
> > end with
> > Sheets("Sheet2").Range("A").value = .Range("B" & Newrow)
> > Sheets("Sheet2").Range("B").value = .Range("E" & Newrow)
> > Sheets("Sheet2").Range("C").value = .Range("F" & Newrow)
> > Sheets("Sheet2").Range("D").value = .Range("G" & Newrow)
> > Sheets("Sheet2").Range("E").value = .Range("H & Newrow)
> > Sheets("Sheet2").Range("F").value = .Range("K" & Newrow)
> > Sheets("Sheet2").Range("G").value = .Range("L" & Newrow)
> > .activate
> > End With
> > End If
> >
> > End Sub
> >
> > "Jock" wrote:
> >
> > > Nice one.
> > > Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only
> > > That would help my cause!
> > >
> > > Thanks
> > > --
> > > Traa Dy Liooar
> > >
> > > Jock
> > >
> > >
> > > "Joel" wrote:
> > >
> > > > Use pastespecial to pate values only
> > > >
> > > > Private Sub CommandButton1_Click()
> > > >
> > > > If ActiveCell.Value = "CH" Then
> > > > RowNo = ActiveCell.Row
> > > > With ActiveSheet
> > > > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > > > End With
> > > > With Sheets("Sheet2")
> > > > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > > > NewRow = Lastrow + 1
> > > > CopyRange.Copy
> > > > .Range("A" & NewRow).pastespecial paste:=xlPasteValues
> > > > .activate
> > > > End With
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > "Jock" wrote:
> > > >
> > > > > That works a treat, thanks.
> > > > > Is it possible to just paste values ie, no formatting?
> > > > > Also can the user be taken to Sheet 2 when the button is clicked? That would
> > > > > help a lot too! There's already a button there to return the user to the
> > > > > active cell on Sheet 1.
> > > > >
> > > > > Thanks
> > > > > --
> > > > > Traa Dy Liooar
> > > > >
> > > > > Jock
> > > > >
> > > > >
> > > > > "Joel" wrote:
> > > > >
> > > > > > Private Sub CommandButton1_Click()
> > > > > >
> > > > > > If ActiveCell.Value = "CH" Then
> > > > > > RowNo = ActiveCell.Row
> > > > > > With ActiveSheet
> > > > > > Set CopyRange = .Range("A" & RowNo & ":K" & RowNo)
> > > > > > End With
> > > > > > With Sheets("Sheet2")
> > > > > > Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
> > > > > > NewRow = Lastrow + 1
> > > > > > CopyRange.Copy Destination:=.Range("A" & NewRow)
> > > > > > End With
> > > > > > End If
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > "Jock" wrote:
> > > > > >
> > > > > > > Hi Joel,
> > > > > > > Good thinking about the button to reduce wrong entries. The drop down list
> > > > > > > is a Data Validation and is located in each cell in column 'M' on Sheet 1.
> > > > > > >
> > > > > > > Cheers,
> > > > > > > --
> > > > > > > Traa Dy Liooar
> > > > > > >
> > > > > > > Jock
> > > > > > >
> > > > > > >
> > > > > > > "Joel" wrote:
> > > > > > >
> > > > > > > > there are two way of doing theis. First to use a worksheet change function
> > > > > > > > when the drop down box is changed to move the data. but this is not really a
> > > > > > > > great way of doing this because if the wrong value is selected unwanted data
> > > > > > > > will be copied. the second method is to use a Button to perform the
> > > > > > > > operation after the value is selected which willreduce the number of wrong
> > > > > > > > items theat will be moved.
> > > > > > > >
> > > > > > > >
> > > > > > > > 1) Is the Drop down List a Data validation or an Autofilter?
> > > > > > > >
> > > > > > > > 2) What cell is the Drop down list Located?
> > > > > > > >
> > > > > > > >
> > > > > > > > "Jock" wrote:
> > > > > > > >
> > > > > > > > > Hi, I posted a question here but recieved no responses so I shall re-phrase
> > > > > > > > > the question in the hope that someone will take the bait.
> > > > > > > > >
> > > > > > > > > Sheet 1. When a specific value (CH) is selected from a drop down list (in
> > > > > > > > > column M), I would like to have all the data from cells on the same row (A-K)
> > > > > > > > > copied to the next empty row in Sheet 2.
> > > > > > > > > So, the theory is that data is entered by the user in cells A-M. When the
> > > > > > > > > option 'CH' is selected from the list of options in cell M, A-K is copied to
> > > > > > > > > Sheet 2 and the user is taken to Sheet 2 to add text if necessary.
> > > > > > > > > Can someone advise if this is achievable or do I have to try a different
> > > > > > > > > approach?
> > > > > > > > > Thanks.
> > > > > > > > > --
> > > > > > > > > Traa Dy Liooar
> > > > > > > > >
> > > > > > > > > Jock