excel - 从一个工作簿粘贴到另一个工作簿时如何设置我的复制范围？

Question

如何设置从单元格 B28 到 Q33 的复制范围？我正在尝试将特定范围的单元格从一个工作簿复制到另一个工作簿。使用下面的代码，我不断复制大部分工作表。

Sub Button1_Click()

    Dim wsSource As Worksheet, wsTitle As Worksheet
    Set wsSource = Workbooks("New Initiative Template v1_30_2019.xlsm").Worksheets(2)
    Set wsTarget = Workbooks("PMO Automation.xlsm").Worksheets(2)

    Dim sourceTitle As Range
    Set sourceTitle = wsSource.Range(wsSource.Range("C30"), wsSource.Cells(Rows.Count, 30).End(xlUp))

    Dim sourcePjstatus As Range
    Set sourcePjstatus = wsSource.Range(wsSource.Range("Q1"), wsSource.Cells(Rows.Count, 33).End(xlUp))

    sourceTitle.Copy Destination:=wsTarget.Cells(Rows.Count, 2).End(xlUp).Offset(2)
    sourcePjstatus.Copy Destination:=wsTarget.Cells(Rows.Count, 3).End(xlUp).Offset(2)

End Sub

标签： excelvba

Answer 1

Option Explicit

Sub Button1_Click()

    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim wb1 As String
    Dim wb2 As String
    Dim sourceTitle As Range
    Dim sourcePjstatus As Range

    wb1 = "New Initiative Template v1_30_2019.xlsm"
    wb2 = "PMO Automation.xlsm"

    Set wsSource = Workbooks(wb1).Worksheets(2)
    Set wsTarget = Workbooks(wb2).Worksheets(2)

    Debug.Print """sourceTitle"""
    Debug.Print vbTab; "Source Range:"
    Debug.Print vbTab; wsSource.Range(wsSource.Range("C30"), wsSource.Cells(Rows.Count, 30).End(xlUp)).Address
    Debug.Print vbTab; "Target Cell:    "
    Debug.Print vbTab; wsTarget.Cells(Rows.Count, 2).End(xlUp).Offset(2).Address
    Debug.Print
    Debug.Print """sourcePjstatus"""
    Debug.Print vbTab; "Source Range:"
    Debug.Print vbTab; wsSource.Range(wsSource.Range("Q1"), wsSource.Cells(Rows.Count, 33).End(xlUp)).Address
    Debug.Print vbTab; "Target Cell:    "
    Debug.Print vbTab; wsTarget.Cells(Rows.Count, 2).End(xlUp).Offset(2).Address
    Debug.Print

    'refers to the last cell in column AD: wsSource.Cells(Rows.Count, 30).End(xlUp)
    'correct? intended?
    Set sourceTitle = wsSource.Range(wsSource.Range("C30"), wsSource.Cells(Rows.Count, 30).End(xlUp))

    'refers to the last cell in column AG: wsSource.Cells(Rows.Count, 33).End(xlUp))
    'correct? intended?
    Set sourcePjstatus = wsSource.Range(wsSource.Range("Q1"), wsSource.Cells(Rows.Count, 33).End(xlUp))

    sourceTitle.Copy Destination:=wsTarget.Cells(Rows.Count, 2).End(xlUp).Offset(2)
    sourcePjstatus.Copy Destination:=wsTarget.Cells(Rows.Count, 3).End(xlUp).Offset(2)

End Sub

您的代码确实有效。
我估计您不打算复制您处理的单元格：

“来源标题”
    来源范围：
    $C$1:$AD$30
    目标细胞：
    $B$5

“sourcePjstatus”
    来源范围：
    $Q$1:$AG$1
    目标细胞：
    $B$5

复制方法的定义很简单：

copyRange.复制目标：=destinationRange

Sub CopySample()
Dim copyRange as Range
Dim destinationRange as Range

set copyRange        = Workbooks("workbook1.xlsx").Sheets("Sheet1").Range("A1:D4")
set destinationRange = Workbooks("workbook2.xlsx").Sheets("Sheet2").Range("B8")

copyRange.Copy Destination:=destinationRange
End Sub

结果截图如下：