代码之家 › 专栏 › 技术社区 › Chris

从一个工作簿粘贴到另一个工作簿时,如何设置复制范围?

vba excel

Chris · 技术社区 · 6 年前

如何设置从单元格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

1 回复 | 直到 6 年前

simple-solution 6 年前

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

你的代码确实有效。
我估计您不打算复制您要寻址的单元格:

"sourceTitle"
    Source Range:
    $C$1:$AD$30
    Target Cell:
    $B$5

"sourcePjstatus"
    Source Range:
    $Q$1:$AG$1
    Target Cell:
    $B$5

复制方法的定义很简单:

文案范围。复制目的地:=目的地范围

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

下面是结果的屏幕截图: