excel - VBA Excel-将数据更新到另一个工作表

Question

我是使用 excel vba 的新手，我创建了一个用户表单（Collection_form），我将从验证列表中输入来自不同成员的不同金额。目标：我想在单击提交按钮后根据来自不同工作表（Collection_Report）表中的集合表单的输入更新所有单元格。单击提交按钮后数据应成功更新并清除内容（Collection_form），以便为下一个成员捕获数据。

Submit Code:
Sub Submit_Click()
'
' Submit_Click Macro
'

'

'name

    Sheets("Collection_Form").Select

    Range("E14:G14").Select
    Selection.Copy
    Sheets("Collection_Report").Select
    Range("B1000").Select
    Selection.End(xlUp).Select

ActiveCell.Offset(1, 0).Select

'ActiveCell.Offset(0, 1).Select
'ActiveSheet.Paste
'ActiveCell.Offset(0, -1).Select
'ActiveCell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Month

Sheets("Collection_Form").Select
Range("E9:F9").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Attendance
Sheets("Collection_Form").Select
Range("E11").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False



'Loan Interest

Sheets("Collection_Form").Select
Range("AV17").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Loan Payment
Sheets("Collection_Form").Select
Range("AV18").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'MGR
Sheets("Collection_Form").Select
Range("AV19").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'SHARES
Sheets("Collection_Form").Select
Range("AV20").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'INSURANCE
Sheets("Collection_Form").Select
Range("AV21").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


'OTHER
Sheets("Collection_Form").Select
Range("AV22").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'MEMBERS
Sheets("Collection_Form").Select
Range("AV23").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


'FINE
Sheets("Collection_Form").Select
Range("AV24").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


'BANK TOTAL
Sheets("Collection_Form").Select
Range("G25").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'CASH TOTAL
Sheets("Collection_Form").Select
Range("H25").Select
Selection.Copy
Sheets("Collection_Report").Select
ActiveCell. Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False


    ActiveWorkbook. Save
MsgBox ("Record Saved")

End Sub

标签： excelvba