Copy range to other sheet depending on entry in cell

Hello,

I recently discovered VBA.
I want to write values froms cells C4:C10 to another sheet, depending on the number given in C2.
On the next worksheet (ws2), I have number 1 to 10. If the number in the first row is the same as given in C2, then the values (C4:10) need to be copied in the cells below that number.

I already got a code to find the the same number:

Private Sub CommandButton1_Click()
Dim FindString As String
Dim Rng As Range
FindString = Sheets("ws1").Range("c2").Value
If Trim(FindString) <> "" Then
With Sheets("ws2").Range("B1:K1")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub

Many thanks! *How can I add an attachment to this post?
screenshot excel test.png screenshot excel test2.png Test.xlsm

Try:

Private Sub CommandButton1_Click()
Dim FindString As String
Dim Rng As Range
FindString = Sheets("ws1").Range("c2").Value
If Trim(FindString) <> "" Then
With Sheets("ws2").Range("B1:K1")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
  Sheets("ws1").Range("C4:C10").copy Rng.offset(1,0)
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub

and please edit your post to comply with http://www.excelforum.com/forum-rule...rum-rules.html by using code tags

As Destination is first (and only) parameter in Range.Copy method name of the parameter can be skipped, so

Sheets("ws1").Range("C4:C10").Copy Destination:=Rng.Offset(1, 0)

is exactly the same as:

Sheets("ws1").Range("C4:C10").Copy Rng.Offset(1, 0)

see how you use MsgBox - for instance you wrote:

MsgBox "Nothing found"

extended version would be:

MsgBox Prompt:="Nothing found"

---------------

Glad it worked, and also from code tags usage in post #3. What I asked before (and ask again) is to push "Edit Post" button below your first post and use code tags also there.

PS. As you have action for both "found" and "not found" have a look on soch "inversion" in the code:

            If Rng Is Nothing Then
                MsgBox "Nothing found", vbExclamation
            Else
                 Sheets("ws1").Range("C4:C10").Copy Rng.Offset(1, 0)
            End If

I added also second (optional) parameter to MsgBox to emphasize that it is a warning message

Glad to hear that.
So if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.