Results 1 to 9 of 9

Thread: [RESOLVED] [Excel 2007] - New to VBA facing this error

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    10

    Resolved [RESOLVED] [Excel 2007] - New to VBA facing this error

    Please help.

    Unable to resolve this:

    curSheet.Cells.Select

    gives me an error:

    Run-time error '1004'
    Select method of Range class Failed.

    What am I doing wrong?

    Thanks,
    Attached Images Attached Images  
    Last edited by akaVB; May 2nd, 2012 at 12:50 PM. Reason: Title description

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: New to VBA facing this error

    Welcome to VBForums

    Thread moved from the 'VB6' forum to the 'Office Development/VBA' forum (while VBA and VB6 have some similarities, they are not the same thing)

  3. #3
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: New to VBA facing this error

    Welcome to the Forums!

    I think the problem is you have both operations a bit mixed, try this: First do everything related to COPY, after that do everything related to PASTE. Example running it only once, you need to adjust this to run inside your While loop:
    Code:
    Public Sub test()
    Dim cursheet As Worksheet, newsheet As Worksheet, oldBook As Workbook, newbook As Workbook
        
        'COPY
        Set oldBook = Application.ActiveWorkbook
        Set cursheet = oldBook.Worksheets(1)
        cursheet.Cells.Select
        Application.Selection.Copy
         
        'PASTE
        Set newbook = Application.Workbooks.Add
        Set newsheet = newbook.Worksheets.Add(after:=newbook.Worksheets(newbook.Worksheets.Count))
        newsheet.Paste
    End Sub

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    10

    Re: New to VBA facing this error

    When running the 'test', still getting the same error at the same point: cursheet.Cells.Select

  5. #5
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: New to VBA facing this error

    Weird, that test() Macro works fine for me. I'm using Office 2003, what version are you using?

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    10

    Re: New to VBA facing this error

    I have office 2007 and 2010. The problem may be related to the later versions of office. I sent the macro to my son to run it and like you, he also did not have any error.

  7. #7

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    10

    Re: New to VBA facing this error

    Quote Originally Posted by akaVB View Post
    I have office 2007 and 2010. The problem may be related to the later versions of office. I sent the macro to my son to run it and like you, he also did not have any error.
    I had another person try this on his computer running Office 2007. He DID NOT have this problem. WEIRD!!

    Need VBA Guru to step in please....

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [Excel 2007] - New to VBA facing this error

    Try changing this bit:
    Code:
        cursheet.Cells.Select
        Application.Selection.Copy
    ..to this:
    Code:
        cursheet.Cells.Copy

  9. #9

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    10

    Re: [Excel 2007] - New to VBA facing this error

    Quote Originally Posted by si_the_geek View Post
    Try changing this bit:
    Code:
        cursheet.Cells.Select
        Application.Selection.Copy
    ..to this:
    Code:
        cursheet.Cells.Copy
    Thank you. It worked. Really appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width