Snasui Excel Forum

Code: Select all

Sub Copy_down()
'
' Copy_down Macro
'

'
    Range("A1:N38").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=21
    Sheets("สรุปวิทยากรตามหลักสูตร").Select
    ActiveWindow.SmallScroll Down:=-30
    Range("A1:N38").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A39").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Code: Select all

Sub Copy_down()
    Dim allV As Range
    Dim r As Range
    Dim ns As Worksheet
    Dim s As Range
    Set ns = Sheets.Add(After:=ActiveSheet)
    Set s = Sheets("สรุปวิทยากรตามหลักสูตร").Range("b6")
    With Sheets("ตารางสรุปประเมินความพึงพอใจ")
        Set allV = .Range("c11", .Range("c" & .Rows.Count).End(xlUp))
    End With
    For Each r In allV
        s.Value = r.Value
        Sheets("สรุปวิทยากรตามหลักสูตร").UsedRange.Copy
        With ns.Range("c" & Rows.Count).End(xlUp).Offset(1, -2)
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
        End With
        Application.CutCopyMode = False
    Next r
End Sub