Snasui Excel Forum

Code: Select all

Sub RecordTraining()
Dim rs As Range, rt As Range
Dim rs1 As Range, rt1 As Range
Dim i As Integer
Worksheets("Form1").Range("L1") _
    = Application.Max(Worksheets("Database") _
        .Range("C:C")) + 1
With Worksheets("Template")
    i = Application.CountIf( _
        .Range("M3:M15"), ">0")
    Set rs = .Range("A2:M" & 12 + i)
    Set rs1 = .Range("A21:K21")
End With
    Set rt = Worksheets("Database") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    Set rt1 = Worksheets("Score") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    rs.Copy
    rt.PasteSpecial xlPasteValues
    rs1.Copy
    rt1.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
MsgBox "Finish"
End Sub

Code: Select all

With Worksheets("Template")
    i = Application.CountIf( _
        .Range("M3:M15"), ">0")
    Set rs = .Range("A2:H" & .Range("N1"))
    Set rs1 = .Range("A21:K21")
End With

Code: Select all

With Worksheets("Template")
    i = Application.CountIf( _
        .Range("M3:M15"), ">0")
    Set rs = .Range("A2:H" & .Range("N1"))
    Set rs1 = .Range("A21:K21")
End With

Code: Select all

With Worksheets("Template")
    i = Application.CountIf( _
        .Range("M3:M15"), ">0")
    Set rs = .Range("A2:M" & 12 + i)
    Set rs1 = .Range("A21:K" & 21 + .Range("A19") - 1)
End With