Run Time error 1004 application defined or object defined error

Question

I am trying to get this code to work but I get the above noted error.  I am trying to have the user in the workbook Inv_SKU_Build.xls ..press a command button which will open the workbook noted copy the contents of colums a,b and c and paste that in sheet 2 of the Inv_SKU_Build.xls workbook.

I get the error and the debugger has the Columns("A"C")Select highlighted


 Private Sub CommandButton1_Click()

Sheets("Form").Select


ChDir "S:\CustomerSupport\Common_Support_Files"
Workbooks.Open Filename:= _
"S:\CustomerSupport\Common_Support_Files\Colour_List_New.xls"

Workbooks("Colour_List_New.xls").Activate
Sheets("Chart_Number").Activate
Columns("A:C").Select
Selection.Copy


Windows("Inv_SKU_Build.xls").Activate
Sheets("Sheet2").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Form").Select

End Sub


The following Macro works fine when run as a macro

Sub Trial()
'
' Trial Macro
' Macro recorded 05/06/2008 by Lane
'

'
Sheets("Form").Select
ChDir "S:\CustomerSupport\Common_Support_Files"
Workbooks.Open Filename:= _
"S:\CustomerSupport\Common_Support_Files\Colour_List_New.xls"
Columns("A:C").Select
Selection.Copy
Windows("Inv_SKU_Build.xls").Activate
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Form").Select
End Sub

I have done a fair bit of reading in the various forums and have tried many variations to try and get the focus on the right object at the right time but so far I am missing the point somehow. 

Can anyone help me with this.

Tx

---

Newbie

Answer 1

Try something like this:

Private Sub CommandButton1_Click()

Dim wbThis As Workbook
Set wbThis = ActiveWorkbook

Dim wbColorList As Workbook
Set wbColorList = Workbooks.Open Filename:= _
"S:\CustomerSupport\Common_Support_Files\Colour_List_New.xls"

Dim wsChartNumber As Worksheet
Set wsChartNumber = wbColorList.Sheets("Chart_Number")

Dim rngFirst3Cols As Range
Set rngFirst3Cols = wsChartNumber.Range(wsChartNumber.Cells(1,1), wsChartNumber.Cells(wsChartNumber.UsedRange.Rows.Count, 1))

rngFirst3Cols.Copy(Me.Sheets("Sheet2").Cells(1,1))

End Sub