copy specific data to another worksheet

[Jock]

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.

 

[Joel]

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]

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,

 

[Joel]

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]

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

 

[Joel]

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]

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

Thanks

 

[Joel]

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]

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!

 

[Joel]

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

 

[Joel]

Sorry, there were errors in the code. Try this

Private Sub CommandButton1_Click()

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

End Sub

 

[Jock]

Hi Joel,
Still wrestling with this. I get a Compile error message: Invalid or
unqualified reference with the ".Range" highlighted in this first row:
Sheets("Sheet2").Range("B").Value = .Range("B" & Newrow)

Cheers

 

[Joel]

Look at thje newer code I sent. I saw that problem earlier this morning.

 

[Jock]

Thanks Joel, got there in the end!

 

[Neon520]

Hi Joel,

I'm looking for solution to a similar problem that Jock is having and hoping
that you could help me tweak the code a little bit to meet my need.

The main idea is the same as Jock's but here are something else that I need:

1. Instead of "ActiveCell.Row", I'd like to have a code that will go through
entire column (i.e. Col K) of Sheet1 to find the "CH" (as in Jock's case) and
copy and paste it over to Sheet2.
2. I'd also like to clear the content of Sheet1 that has already been copied
to Sheet2. I was thinking of Cut&Paste but the problem with that is it will
Remove the line from Sheet1 and I don't want that since my set up is to
certain number of lines in Sheet1. If we use "ClearContent" code then the
line will look empty without sorting. This is some thought I have based on
my knowledge of how to get this done, but if you have any brilliant solution
to this, that would be great!

I hope you'll receive my message and be willing to help me with this since
you're already solve half of the problem.

Thank you so much!!
Neon520