Excel Macro to copy certain columns from one workbook to another

Question

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

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

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.

Source File

Target File

Answer 1

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