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