在一個檔案中，我有資料（Zeszyt.xlsm - Sheet1），在另一個空檔案（Sheet2）中，有相同的標題和填入按鈕。然而，當我按下按鈕。沒有錯誤，但沒有補充。你能幫助我嗎？
Private Sub CommandButton2_Click()
Dim wb As Workbook
ThisWorkbook.Worksheets("Sheet1").Rows(12) 。
選擇.復制
Set wb = Workbooks.Open("C:UsersadminDesktopTESTeszyt2.xlsm")
wb.Worksheets("Sheet2").激活
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) .Row
ActiveSheet.Cells(lastrow 1, 1).Select。
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close savehanges = True.
Set wb = Nothing
ThisWorkbook.Worksheets("Sheet1").激活
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select。
Application.CutCopyMode = False.
End Sub
uj5u.com熱心網友回復：
不需要選擇或復制/粘貼。
首先，我建議把所有的引數如作業簿名稱等作為常量放在模塊的頭部。這樣一來，解決重命名等問題就容易多了。
通過擁有一個通用的 copyRangeValues 程式，你也可以將這個子程式重新用于其他的復制操作：
Option Explicit
'config source
Private Const wsSourceName As String = "Sheet1"
Private Const rowToCopy As Long = 12 '這真的總是12行? ??
Private Const wbTargetName As String = "C:UsersadminDesktopTESTeszyt2.xlsm"
Private Const wsTargetName As String = "Sheet2"
Private Sub CommandButton2_Click()
'第一步：準備你的來源范圍。
Dim wbSource As Workbook
Set wbSource = ThisWorkbook
Dim wsSource As Worksheet
Set wsSource = wbSource.Worksheets(wsSourceName)
Dim rgSource As Range
Set rgSource = wsSource.Rows(rowToCopy)
'第二步：準備你的左上角目標單元格。
Dim wbTarget As Workbook
Set wbTarget = Workbooks.Open(wbTargetName)
Dim wsTarget As Worksheet
Set wsTarget = wbTarget.Worksheets(wsTargetName)
Dim lastRow As Long
LastRow = wsTarget.UsedRange.Rows.Count
Dim rgTargetCell As Range
Set rgTargetCell = wsTarget.Cells(lastRow 1, 1)
'第三步：復制范圍 - 使用通用例程。
copyRangeValues rgSource, rgTargetCell
第四步：關閉目標作業簿。
wbTarget.Close saveChanges:=True。
End Sub
'把這個放在一個一般的模塊里。
Public Sub copyRangeValues(rgSource As Range, rgTargetCell As Range)
'通用程式，將一個范圍復制到另一個范圍。
'rgTargetCell = 目標范圍的左上角。
Dim rgTarget As Range
'根據rgSource的尺寸調整rgTarget的大小。
With rgSource
Set rgTarget = rgTargetCell.Resize(.Rows.Count, .Columns.Count)
結束 與
'從rgSource寫值到rgTarget - 無需復制/粘貼!!!
'格式不被復制 - 只有值。
rgTarget.Value = rgSource.Value
End Sub
uj5u.com熱心網友回復：
復制行到另一個檔案
Activate和Select，代碼將運行得更慢。但如果你使用變數，則不會。
Option Explicit>
Private Sub CommandButton2_Click()
Const swsName As String = "Sheet1"
Const sRow As Long = 12
Const dFilePath As String _
= "C:UsersadminDesktopTESTeszyt2.xlsm"。
Const dwsName As String = "Sheet2"
Dim swb As 作業簿。Set swb = ThisWorkbook
Dim sws As 作業表。Set sws = swb.Worksheets(swsName)
Dim srg As Range: Set srg = sws.Rows(sRow)
Application.ScreenUpdating = False
Dim dwb As Workbook: Set dwb = Workbooks.Open(dFilePath)
Dim dws As 作業表。Set dws = dwb.Worksheets(dwsName)
Dim dCell As Range
Set dCell = dws.Cells(dws.Rows.Count, "A").End(xlUp) .Offset(1)
srg.Copy Destination:=dCell
dwb.Close SaveChanges:=True。
Application.ScreenUpdating = True"完成。", vbInformation, "追加行"。
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/307758.html
標籤：