Excel宏将某些列从一个工作簿复制到另一工作簿 [英] Excel Macro to copy certain columns from one workbook to another

问题描述

这是我第一次尝试编写VBA代码.我模仿的是我在 stackoverflow 上找到的内容

This is my first attempt to write VBA code. I am mimicking something I found on stackoverflow.

我想将某些列(A，B和E)从一个工作簿复制到另一个工作簿，还希望更改某些行的字体和颜色，并在某些单元格中编辑文本(将长短语替换为组"一词)

I want to copy certain columns (A, B and E) from one workbook to another and also change the font and color of certain Rows and edit the text in certain cells (replace a long phrase with the word "Group").

这是我复制的没有更改的代码:

This is the code I copied without change:

Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Workbooks("Source").Worksheets("Sheet1").Columns("A")
Set targetColumn = Workbooks("Target").Worksheets("Sheet1").Columns("A")

sourceColumn.Copy Destination:=targetColumn
End Sub

我收到一个运行时错误9，下面的行突出显示:

I get a runtime error 9 and the line below is highlighted:

Set sourceColumn = Workbooks("Source").Worksheets("Sheet1").Columns("A")

我在下面附加源文件和目标文件，希望它们在成功运行结束时看起来像.

I am attaching the Source and Target files below as I hope they would look like at the end of a successful run.

源文件

目标文件

推荐答案

您引用的表格不存在.通过使用索引将其更改为引用工作簿中的第一张工作表.您也没有包括该文件的扩展名，因此它也将在工作簿对象上失败.

You reference a sheet that is not there. Change it to reference the first sheet in the workbook by using its index. You also did not include the extension to the file so it would fail on the workbook object as well.

Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Workbooks("Source.xlsm").Worksheets(1).Columns("A")
Set targetColumn = Workbooks("Target.xlsm").Worksheets(1).Columns("A")

sourceColumn.Copy Destination:=targetColumn
End Sub

这篇关于Excel宏将某些列从一个工作簿复制到另一工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！