使单元格范围等于不同工作簿中的另一个单元格范围 [英] Make range of cells equal to another range of cells in different workbooks

问题描述

我想从多个工作簿中获取一系列值，然后将其输入到主工作簿的下一个空白行中.我正在尝试尽可能地简化此代码，因为将有多个工作簿输入到Masterwork簿的相应选项卡中.

I would like to take a range of values from multiple workbooks and enter it into the next blank row of a master workbook. I’m trying to streamline this code as much as possible as there will be multiple workbooks that will be entered into its corresponding tab in the Masterwork book.

示例:

Column C (MasterWorkbook,Sheet 2) = Column A (Workbook 1, Sheet 1)

问题:

1. 如果行数每天都会变化，是否可以将一个单元格范围设为另一个范围?
2. 是否有一种简单的方法可以在代码中引用工作簿和工作表变量?例如: wk1.sht1.Origin.Value = wk2.sht2.Destination.Value

代码:

Sub CopyData()


Dim wk1 As Workbook
Dim wk2 As Workbook
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim Origin As Range
Dim Destination As Range

Set wk1 = Workbooks("Device")
Set wk2 = Workbooks("Pri Access Master)
Set sht1 = Worksheets(1)
Set sht2 = Worksheets("Device")

Set Origin = Range("B2").End(xlDown)
Set Destination = Range("D120000").End(xlUp).Offset(1, 0)

'(Isn't working)Copy data in column B and paste in next blank row of another    workbook in another column
wk2.sht2.Destination.Value = wk1.sht1.Origin.Value


End Sub

推荐答案

您对对象的引用不正确-设置范围对象后，您无需包括完整的路径":

Your referencing of objects is incorrect - you dont need to include the full "path" once you have set the Range objects:

Sub CopyData()


    Dim wk1 As Workbook
    Dim wk2 As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim Origin As Range
    Dim Destination As Range

    Set wk1 = Workbooks("Device")
    Set wk2 = Workbooks("Pri Access Master")
    Set sht1 = wk1.Worksheets(1)
    Set sht2 = wk2.Worksheets("Device")
    Set Destination = sht2.Range("D120000").End(xlUp).Offset(1, 0)

    'EDIT - dynamic copy range
    Set Origin = sht1.Range(sht1.Range("B2"), sht1.Cells(sht1.Rows.Count, "B").End(xlUp))



    Destination.Resize(Origin.Rows.Count, Origin.Columns.Count).Value = _
                                                           Origin.Value
End Sub

这篇关于使单元格范围等于不同工作簿中的另一个单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！