Excel macro copy columns from one workbook to another

Question

I have a workbook: "Check for logging". From where I want to copy column "A" and paste it in workbook: "Unit test template". In column "A".

I have been trying to use following code:

Private Sub CopyColumnsButton_Click()

Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Workbooks("Check for logging.xlsm").Worksheets("Parameters").Columns("A")
Set targetColumn = Workbooks("Unit test template.xlsm").Worksheets("Sheet1").Columns("A")

sourceColumn.Copy Destination:=targetColumn

End Sub

Though every time I attempt to run the macro, I get a message saying: "Run-time error '9': Subscript out of range" and then this line is highlighted:

Set targetColumn = Workbooks("Unit test template.xlsm").Worksheets("Sheet1").Columns("A")

Both workbooks are open when I attempt to run the macro.

I would be very thankfull if anyone could help

Your code "works on my machine" (with test workbooks, of course). Do you have any special settings in Excel? — JensS

kaza kaza · Accepted Answer · 2017-08-25T12:59:01

"Run-time error '9': Subscript out of range".

It is mostly unable to find one of the two "Unit test template.xlsm" or "Sheet1" within the workbook.

Workbook issue..
1. Check if the file name is "Unit test template.xlsx" (not .xlsm)
2. Check if the file "Unit test template.xlsm" is open in the same session as "Check for logging.xlsm" (If Ctrl+Tab doesn't take you to the next workbook, that means they are open in two different session of Excel Application)
3. The Filename is slightly off.

Worksheet issue..
1. Check if "Sheet1" was deleted. So it could actually be "Sheet2" etc...

Excel macro copy columns from one workbook to another

1 Answers