locked
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
    Friday, June 06, 2008 2:17 PM

Answers

  • 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

    • Marked as answer by Bruno Yu Thursday, June 12, 2008 7:33 AM
    Saturday, June 07, 2008 1:54 AM