+ Reply to Thread
Results 1 to 5 of 5

Error in VBA

  1. #1
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Error in VBA

    Hi,
    Can you please help me with the below code?
    PHP Code: 
    Sub Collate_Sheets()


        
    Sheets("Plan&Status").Select
        Range
    ("A1").Select
        Range
    (SelectionActiveCell.SpecialCells(xlLastCell)).Select
        Selection
    .Copy
        Sheets
    ("Consolidated").Select
        ActiveSheet
    .Paste
        ActiveCell
    .SpecialCells(xlLastCell).Select
        
    ' I need to select one cell below, and the cell in column A at this point
        Sheets("Delivered_Projects").Select
        Range("A2").Select
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Consolidated").Select
        ActiveSheet.Paste
    End Sub 
    I am getting the following error while executing this macro
    Snap21.jpg

    Please help me

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Error in VBA

    Maybe:

    Please Login or Register  to view this content.

    Note: you should adjust the width of the range. That is, change .Range("A1:IV" & lLR).Copy _ to reflect the number of columns you have.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Error in VBA

    Perhaps a better version without the repetition:

    Please Login or Register  to view this content.

    Regards, TMS
    Last edited by TMS; 05-31-2013 at 03:45 AM.

  4. #4
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: Error in VBA

    Hi,
    Thanks for your help
    But I tried to put that in the below file in Consolidated tab.
    But is not coming as expected. Can you please help
    Tracker.xlsm

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Error in VBA

    Couple of problems. 1) you have a single cell Title so the code to determine the last column wouldn't work correctly. 2) The sheet names in the Tracker spreadsheet are different to the sample provided (with no spaces and/or underscores).

    Try this in a standard module (not in a worksheet class code module)

    Please Login or Register  to view this content.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1