如何编程命令按钮将一系列数据复制到另一个工作簿 [英] How to program command button to copy a range of data to another workbook
本文介绍了如何编程命令按钮将一系列数据复制到另一个工作簿的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
目前，当点击命令按钮2时，在时间表上捕获的数据将被传输到同一工作表中名为数据的工作表。我想要的是将其传输到另一个文件夹中保存的另一个工作簿。工作表的完整路径为C：\Users\mohskhan\Desktop\masterts.xlsm。请有人帮忙
当前编码如下：
Private Sub CommandButton2_Click（）
ComboBox1.Enabled = True
Dim ssheet As Worksheet
设置ssheet = ThisWorkbook.Sheets（Data）
nr = ssheet.Cells（Rows.Count，1）.End（xlUp）.Row + 1
ssheet.Cells（nr，1）= CDate（Me.TextBox1）
ssheet.Cells（nr，2）=（Me.TextBox2）
ssheet.Cells（nr，3）=（Me.ComboBox1）
ssheet.Cells（nr，4）=（Me.ComboBox2）
ssheet.Cells（nr，5）=（Me.TextBox3）
ssheet.Cells（nr，6）=（Me.TextBox4）
ssheet.Cells（nr，7）=（ Me.TextBox5）
ssheet.Cells（nr，8）=（Me.TextBox12）
ssheet.Cells（nr，9）=（Me.ComboBox3）
ssheet.Cells（nr， 11）= Evaluate（= NOW（） - TODAY（））
ssheet.Cells（nr，14）=（Me.TextBox35）
ssheet.Cells（nr，21）=（Me。 TextBox6）
ssheet.Cells（nr，22）=（Me.ComboBox4）
ssheet.Cells（nr，23）=（Me.TextBox7）
ssheet.Cells（nr，24） =（Me.TextBox23）
ssheet.Cells（nr，25）=（Me.TextBox8）
ssheet.Cells（nr，26）=（Me.ComboBox5）
ssheet.Cells（nr，27）=（Me.TextBox9）
ssheet.Cells（nr，28）=（Me.TextBox24）
ssheet.Cells（nr，29）=（Me.TextBox10）
ssheet.Cells（nr，30）=（Me.ComboBox6）
ssheet.Cells（nr， 31）=（Me.TextBox11）
ssheet.Cells（nr，32）=（Me.TextBox25）
ssheet.Cells（nr，34）=（Me.TextBox36）
ssheet。单元格（nr，35）=（Me.TextBox37）
ComboBox1 =
ComboBox2 =
ComboBox3 =
TextBox3 =
TextBox4 =
TextBox12 =
TextBox5 =
ComboBox4 =
ComboBox5 =
ComboBox6 =
TextBox6 =
TextBox7 =
TextBox8 =
TextBox9 =
TextBox10 =
TextBox11 =
TextBox23 =
TextBox24 =
TextBox25 =
TextBox35 =
TextBox36 =
TextBox37 =
CommandButton1.Enabled = False
CommandButton2.Enabled = False
End Sub
解决方案
更新：
Private Sub CommandButton2_Click（）
Const FullName =C：\Users\ Owner \Downloads\masterts.xlsm
Dim CloseWorkbook As Boolean
Dim WB As Workbook
ComboBox1.Enabled = True
错误恢复Next
设置WB =工作簿（masterts.xlsm）
CloseWorkbook = Err.Number = 0
打开错误GoTo 0
如果WB不是，然后设置WB = Workbooks.Open（FullName）
使用WB.Worksheets（Data）
带.Range（A& .Rows.Count）.End（xlUp）.Offset（1）
.Resize（1，13）.Value = Array（CDbl（CDT（Me.TextBox1）），Me.TextBox2，Me.ComboBox1.Value ，Me.ComboBox2.Value，Me.TextBox3.Value，Me.TextBox4.Value，_
Me.TextBox5.Value，Me.TextBox12.Value，Me.ComboBox3.Value，CDbl（TimeValue（Now））， Me.TextBox35.Value，Me.TextBox6.Value，Me.ComboBox4.Value）
结束
带.Range（U& .Rows.Count）.End（xlUp）.Offset（ 1）
.Resize（1，12）.Value = Array（Me.TextBox7.Value，Me.TextBox23.Value，Me.TextBox8.Value，Me.ComboBox5.Value，Me.TextBox9.Value，Me。 TextBox24.Value，Me.TextBox10.Value，Me.ComboBox6.Value，Me.TextBox11.Value，Me.TextBox25.Value，Me.TextBox36.Value，Me.TextBox37.Value）
结束
.Save
结束
如果CloseWorkbook然后WB.Close SaveChanges：= False
CommandButton1.Enabled = False
CommandButton2.Enabled = False
End Sub
I wonder if someone can help me please. I am a new VBA user and made a timesheet using guidelines on this website.
Currently, when the command button 2 is clicked, the data captured on the timesheet is transferred to sheet called 'Data' within the same worksheet. What I want is to transfer it to another workbook's sheet saved in another folder. Full path of sheet is C:\Users\mohskhan\Desktop\masterts.xlsm. Please can someone help.
Current coding is as follows:
Private Sub CommandButton2_Click()
ComboBox1.Enabled = True
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("Data")
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
ssheet.Cells(nr, 1) = CDate(Me.TextBox1)
ssheet.Cells(nr, 2) = (Me.TextBox2)
ssheet.Cells(nr, 3) = (Me.ComboBox1)
ssheet.Cells(nr, 4) = (Me.ComboBox2)
ssheet.Cells(nr, 5) = (Me.TextBox3)
ssheet.Cells(nr, 6) = (Me.TextBox4)
ssheet.Cells(nr, 7) = (Me.TextBox5)
ssheet.Cells(nr, 8) = (Me.TextBox12)
ssheet.Cells(nr, 9) = (Me.ComboBox3)
ssheet.Cells(nr, 11) = Evaluate("=NOW()-TODAY()")
ssheet.Cells(nr, 14) = (Me.TextBox35)
ssheet.Cells(nr, 21) = (Me.TextBox6)
ssheet.Cells(nr, 22) = (Me.ComboBox4)
ssheet.Cells(nr, 23) = (Me.TextBox7)
ssheet.Cells(nr, 24) = (Me.TextBox23)
ssheet.Cells(nr, 25) = (Me.TextBox8)
ssheet.Cells(nr, 26) = (Me.ComboBox5)
ssheet.Cells(nr, 27) = (Me.TextBox9)
ssheet.Cells(nr, 28) = (Me.TextBox24)
ssheet.Cells(nr, 29) = (Me.TextBox10)
ssheet.Cells(nr, 30) = (Me.ComboBox6)
ssheet.Cells(nr, 31) = (Me.TextBox11)
ssheet.Cells(nr, 32) = (Me.TextBox25)
ssheet.Cells(nr, 34) = (Me.TextBox36)
ssheet.Cells(nr, 35) = (Me.TextBox37)
ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
TextBox3 = ""
TextBox4 = ""
TextBox12 = ""
TextBox5 = ""
ComboBox4 = ""
ComboBox5 = ""
ComboBox6 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox23 = ""
TextBox24 = ""
TextBox25 = ""
TextBox35 = ""
TextBox36 = ""
TextBox37 = ""
CommandButton1.Enabled = False
CommandButton2.Enabled = False
End Sub
解决方案
Updated:
Private Sub CommandButton2_Click()
Const FullName = "C:\Users\Owner\Downloads\masterts.xlsm"
Dim CloseWorkbook As Boolean
Dim WB As Workbook
ComboBox1.Enabled = True
On Error Resume Next
Set WB = Workbooks("masterts.xlsm")
CloseWorkbook = Err.Number = 0
On Error GoTo 0
If WB Is Nothing Then Set WB = Workbooks.Open(FullName)
With WB.Worksheets("Data")
With .Range("A" & .Rows.Count).End(xlUp).Offset(1)
.Resize(1, 13).Value = Array(CDbl(CDate(Me.TextBox1)), Me.TextBox2, Me.ComboBox1.Value, Me.ComboBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, _
Me.TextBox5.Value, Me.TextBox12.Value, Me.ComboBox3.Value, CDbl(TimeValue(Now)), Me.TextBox35.Value, Me.TextBox6.Value, Me.ComboBox4.Value)
End With
With .Range("U" & .Rows.Count).End(xlUp).Offset(1)
.Resize(1, 12).Value = Array(Me.TextBox7.Value, Me.TextBox23.Value, Me.TextBox8.Value, Me.ComboBox5.Value, Me.TextBox9.Value, Me.TextBox24.Value, Me.TextBox10.Value, Me.ComboBox6.Value, Me.TextBox11.Value, Me.TextBox25.Value, Me.TextBox36.Value, Me.TextBox37.Value)
End With
.Save
End With
If CloseWorkbook Then WB.Close SaveChanges:=False
CommandButton1.Enabled = False
CommandButton2.Enabled = False
End Sub
这篇关于如何编程命令按钮将一系列数据复制到另一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
