Copy Range from active row to new sheet

[hmltnangel]

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

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

[hmltnangel]

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

Amended the code like this

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.

[hmltnangel]

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]

[snb]

Avoid using 'copy' to copy values only.