none
Run Time error 1004 Application defined or Object defined error

    Question

  • Being quite new at this as you can see I am not sure if I have the right forum or not but I am certainly looking for whatever assistance I can get.  I have also posted this in VB Language as well, hoping that someone much brighter than I will take pity on me.


     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  LLH
    Newbie
    Newbie
    Friday, June 06, 2008 4:04 PM

Answers

  • Hi and welcome to this forum.  Unfortunately the VisualBasic forums here are dedicated to VB.NET and not VBA (or VB6) which appears to be what you are working with.  However, there is a thread at the top of this forum with instructions for where you can find assistance with your question.  Here's the link to it...

    http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/6a0719fe-14af-47f7-9f51-a8ea2b9c8d6b

    Good Luck!  On first glance there is nothing wrong with the syntax of Columns("A:C").Select -- it definitely works on its own.  Perhaps there is no workbook activated (i.e. your workbook.Activate call failed, perhaps the filename text argument is wrong?)
    Friday, June 06, 2008 5:42 PM