将外部工作簿XLDOWN复制到当前工作簿

Question

我试图从名为“everett”的名为“DDR”&选项卡的文件中使用XLDOWN从单元格“A9”中复制所有结果并将其粘贴回来到当前正在使用的工作簿中。

任何帮助，将不胜感激

Sub XLDOWN1() 
' 
' XLDOWN1 Macro 
' 

' 
    Dim wbSource As Workbook, wbDest As Workbook 
    Dim wsSource As Worksheet, wsDest As Worksheet 
    Dim rngSource As Range, rngDest As Range 

    Set wbSource = Workbooks.Open("G:\GAGC\Accounting\Payroll\Payroll\Analysis Macro Upload\DDR.xlsx", , True) 
    Set wsSource = wbSource.Worksheets("Everett") 


    ws.Range("A9", Range("A9").End(xlDown)).Select 
    Selection.Copy 



    Set rngSource = wsSource.Range("A9").Range(Selection, Selection.End(xlDown)) 
    Set wbDest = ThisWorkbook 
    Set wsDest = wbDest.Worksheets("2016") 
    Set rngDest = wsDest.Range("A4") 'Destination Cell 

    rngDest.Value = rngSource.Value 'Copies values over only 

    wbSource.Close (False) 'Close without saving changes 


End Sub 

Answer 1

给这个一杆。您提供的代码中有几个问题应该在查看下面重构的代码后清楚。

Dim wbSource As Workbook, wbDest As Workbook 
Dim wsSource As Worksheet, wsDest As Worksheet 
Dim rngSource As Range, rngDest As Range 

'set up source workbook, sheet, range 
Set wbSource = Workbooks.Open("G:\GAGC\Accounting\Payroll\Payroll\Analysis Macro Upload\DDR.xlsx", , True) 
Set wsSource = wbSource.Worksheets("Everett") 
Set rngSource = wsSource.Range(wsSource.Range("A9"), wsSource.Range("A9").End(xlDown)) 

'set up destination workbook, sheet, range 
Set wbDest = ThisWorkbook 
Set wsDest = wbDest.Worksheets("2016") 
Set rngDest = wsDest.Range("A4") 'Destination Cell 

rngSource.Copy Destination:=rngDest 

wbSource.Close False