在Excel-Vba中复制多列

debugcn 发表于 Dev

in那教

嗨，我正在尝试将多个列从一个工作簿复制到另一个工作簿，以下是我如何复制一个工作簿的代码，并且需要帮助以使代码更加优化，因为我不想为所有列编写相同的代码。下面是代码。

Sub Copymc()

Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("H:\testing\demo\test2.xlsx")
Set y = Workbooks.Open("H:\testing\demo\test1.xlsx")
Dim LastRow As Long
Dim NextRow As Long

' determine where the data ends on Column B Sheet1

x.Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row

' copy the data from Column B in Sheet 1

Range("A2:A" & LastRow).Copy

' Determine where to add the new data in Column C Sheet 2

y.Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Offset(1, 0).Select
NextRow = ActiveCell.Row

' paste the data to Column C Sheet 2

y.Worksheets("Sheet1").Range("A" & NextRow).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("A1").Select

End Sub

我试图将所有列都放在range语句中，但是发现的问题是如何粘贴？如何在不重复代码的情况下针对多列执行此操作？提前致谢。

凯尔

假设您要复制广告列：

Sub Copymc()

Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("H:\testing\demo\test2.xlsx")
Set y = Workbooks.Open("H:\testing\demo\test1.xlsx")
Dim LastRow As Long
Dim NextRow As Long

' determine where the data ends on Column B Sheet1

x.Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row

' copy the data from Column B in Sheet 1

Range("A2:D" & LastRow).Copy y.worksheets("Sheet1").range("a65536").end(xlup).offset(1,0)

' Determine where to add the new data in Column C Sheet 2

'y.Worksheets("Sheet1").Activate
'Range("A65536").Select
'ActiveCell.End(xlUp).Offset(1, 0).Select
'NextRow = ActiveCell.Row

' paste the data to Column C Sheet 2

'y.Worksheets("Sheet1").Range("A" & NextRow).Select

'ActiveSheet.Paste

Application.CutCopyMode = False

Range("A1").Select

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。