Copy Range from active row to new sheet

Ok folks, needing a wee bit of help with this. My skills arent the greatest but I have worked this so far - and just need help to finish.

My VB Code is as follows

Code:

---

Sub CopySheet()
    Dim wsMaster2 As Worksheet
    Application.ScreenUpdating = False
    Sheets("MASTER").Copy After:=Sheets(2)
    With Sheets("Master (2)")
        Sheets("Diary System").Range("B5:H5").Copy
        Range("H12").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        .Range("H12,C8") = "123"
        .Range("I12,B5") = "ham"
        .Range("C9") = "Await RM Update"
        .Range("F8:F9") = "Yes"
        .Range("M12,C16,N12,C15") = "6/10/2014"
        With .Range("H12:N12")
        .ClearContents
        End With
        .Visible = False
    End With
    Sheets("Diary System").Select
    Application.ScreenUpdating = True
   
End Sub

---

I need to amend it a bit so that instead of copying this line "Sheets("Diary System").Range("B5:H5").Copy" - It will copy those cells from the active row each time.

Also - not sure how to make it copy the value each time rather than the value thats already in the VB

Doh! almost there but hit another snag....

Amended the code like this

Code:

---

Sub CopySheet()
    Dim wsMaster2 As Worksheet
    Application.ScreenUpdating = False
    Sheets("MASTER").Copy After:=Sheets(2)
    With Sheets("Master (2)")
        Sheets("Diary System").Range("B5:H5").Copy
        Range("H12").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        .Range("C8") = (H12)
        .Range("B5") = (I12)
        .Range("C9") = (J12)
        .Range("F8") = (K12)
        .Range("F9") = (L12)
        .Range("C16") = (M12)
        .Range("C15") = (N12)
        With .Range("H12:N12")
        .ClearContents
        End With
        .Visible = False
    End With
    Sheets("Diary System").Select
    Application.ScreenUpdating = True
   
End Sub

---

But when it does the .clear contents it doesnt retain the info in the other cells.

Slowly but steadily fixing it. I now have the following and my only problem is how to get it to select the range based on the active row.

[VB]Sub CopySheet()
Dim wsMaster2 As Worksheet
Application.ScreenUpdating = False
Sheets("MASTER").Copy After:=Sheets(2)
With Sheets("Master (2)")
Sheets("Diary System").Range("B5:H5").Copy
Range("H12").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Range("H12").Copy
Range("C8").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Range("I12").Copy
Range("B5").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
.Range("J12").Copy
Range("C9").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Range("K12").Copy
Range("F8").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Range("L12").Copy
Range("F9").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Range("M12").Copy
Range("C16").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Range("N12").Copy
Range("C15").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Name = [B5]
.Visible = False
End With
Sheets("Diary System").Select
Application.ScreenUpdating = True

End Sub
[/VB]

Avoid using 'copy' to copy values only.