Snasui Excel Forum

Code: Select all

'Private Sub Worksheet_Change(ByVal Target As Range)
'    Application.EnableEvents = False
'    Workbooks.Open Filename:="c:\Plan\planning.xlsx"
'    Workbooks("planning.xlsx").Worksheets("M01").Range("A3:Q4000").Value = _
'        Workbooks("plannew.xlsm").Worksheets("M01").Range("A3:Q4000").Value
'    ActiveWindow.Close True
'    Application.EnableEvents = True
'End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim InputRange As Range, Target As Range
    Application.EnableEvents = False
    Set InputRange = Range("c2:c17")
    Set Target = Worksheets([C17].Value).Range("b" & Rows.Count).End(xlUp)
    For Each cll In InputRange
        Target.Offset(1, c).Value = cll.Value
        c = c + 1
    Next
    Application.EnableEvents = True
    Workbooks.Open Filename:="c:\Plan\planning.xlsx"
    Workbooks("planning.xlsx").Worksheets("M01").Range("A3:Q4000").Value = _
        Workbooks("plannew.xlsm").Worksheets("M01").Range("A3:Q4000").Value
    Workbooks("planning.xlsx").Close True
    Range("c2:c17").Select
    MsgBox "จัดเก็บข้อมูลเรียบร้อยแล้ว"
    Selection.ClearContents
    Range("C2").Select
    ThisWorkbook.Save
End Sub

Code: Select all

Workbooks("planning.xlsx").Worksheets("M01").Range("A3:Q4000").Value = _
        Workbooks("plannew.xlsm").Worksheets("M01").Range("A3:Q4000").Value
    Workbooks("planning.xlsx").Worksheets("M02").Range("A3:Q4000").Value = _
        Workbooks("plannew.xlsm").Worksheets("M02").Range("A3:Q4000").Value