Close

Results 1 to 9 of 9
  1. #1
    DF Super Moderator Teajunkie's Avatar
    Join Date
    Dec 2009
    Location
    Devon
    Posts
    3,818
    Thanks
    1,670
    Thanked:        1,644
    Karma Level
    498

    Advice Desperate kids excel coursework need help with macro

    Now 5 years ago i could have done this with my eyes closed but after all my treatment i cant remember shit about excel so im hoping you guys can help out?

    Main workbook only has a few sheets and has three boxes to enter details, which then need to be added to a different sheet and on a new line each time.
    she has created a new macro called add which copies three boxes of information and pastes them into the prices page as we need it to but then we have hit a wall.
    This is the macro:
    Code:
    Sub Add()
    '
    ' Add Macro
    '
    
    '
        Range("K24,N24,Q24").Select
        Range("Q24").Activate
        Selection.Copy
        Sheets("prices").Select
        ActiveSheet.Paste
        Sheets("add product page").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        Range("N24").Select
        ActiveCell.FormulaR1C1 = ""
        Range("K24").Select
        ActiveCell.FormulaR1C1 = ""
        Range("H24").Select
        
    End Sub

    She is supposed to add this line of text to the macro called add :
    Code:
      Selection.End(xlDown).Select
    Cells(Selection.Row + 1, Selection.Column).Select
    This is supposed to add the copied text in the macro to the next free row on the product page, so its not overwriting the previous entry.

    we paste that in and always get an error?
    I have added the main workbook called model.zip
    Please anyone can you help this needs to be handed in tomorrow!
    [Only registered and activated users can see links. ]

    PLEASE HELP!!!!
    Find me on Instagram and twitter @mrteajunke.

  2. #2
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,848
    Thanks
    1,034
    Thanked:        783
    Karma Level
    302

    Default Re: Desperate kids excel coursework need help with macro

    Hi

    I have written me own crude function for this (there are other ways)

    Code:
    Function LastRow(sColumn, iStart, sWorksheet) As Integer
        Dim lrn
            lrn = iStart
            Do Until Worksheets(sWorksheet).Range(sColumn & Trim(Str(lrn))) = ""
            lrn = lrn + 1
         Loop
      LastRow = lrn - 1
    End Function
    Then you can just add the following line into your code

    Code:
    Sheets("prices").Select
     Range("A" & CStr(LastRow("A", 2, "prices") + 1)).Select 
    ActiveSheet.Paste

    ;-)

    Thanks to akimba

    Teajunkie (22nd October 2014) 


  3. #3
    DF Super Moderator Teajunkie's Avatar
    Join Date
    Dec 2009
    Location
    Devon
    Posts
    3,818
    Thanks
    1,670
    Thanked:        1,644
    Karma Level
    498

    Default Re: Desperate kids excel coursework need help with macro

    Thanks buddy
    She has to hand it In today, so if she gets an extra day we may be able to use it. otherwise too late �� for her.

    There's a hash symbol at the top of your advanced message box for code tags.


    Sent from my iPhone using some Tapatalk thingy Mabob
    Last edited by Teajunkie; 22nd October 2014 at 10:48 AM.
    Find me on Instagram and twitter @mrteajunke.

  4. #4
    DF VIP Member Undertaker's Avatar
    Join Date
    Nov 2000
    Location
    Earth
    Posts
    2,533
    Thanks
    39
    Thanked:        189
    Karma Level
    425

    Default Re: Desperate kids excel coursework need help with macro

    might just be simpler pasting the values in the last available row, macro could be cut down significantly,

  5. #5
    DF Super Moderator Teajunkie's Avatar
    Join Date
    Dec 2009
    Location
    Devon
    Posts
    3,818
    Thanks
    1,670
    Thanked:        1,644
    Karma Level
    498

    Default Re: Desperate kids excel coursework need help with macro

    Quote Originally Posted by Undertaker View Post
    might just be simpler pasting the values in the last available row
    If only it was that simple!
    Its part of coursework and she has to make the submit and clear button do it automatically.
    Find me on Instagram and twitter @mrteajunke.

  6. #6
    DF VIP Member Undertaker's Avatar
    Join Date
    Nov 2000
    Location
    Earth
    Posts
    2,533
    Thanks
    39
    Thanked:        189
    Karma Level
    425

    Default Re: Desperate kids excel coursework need help with macro

    thats what I meant, in the macro, - paste to last available row


    Sheets("prices").Select
    ActiveSheet.Paste

    here you are assuming that the last row is automatically selected, but it might not be thus you will be overwriting data if the user has decided to select a different row manually in prices sheet

  7. #7
    DF Super Moderator Teajunkie's Avatar
    Join Date
    Dec 2009
    Location
    Devon
    Posts
    3,818
    Thanks
    1,670
    Thanked:        1,644
    Karma Level
    498

    Default Re: Desperate kids excel coursework need help with macro

    Oh right yeah get you now lol
    When she gets home I'll give it a try.

    Thanks lads.
    Mucho apreciato 😉


    Sent from my iPhone using some Tapatalk thingy Mabob
    Find me on Instagram and twitter @mrteajunke.

  8. #8
    DF VIP Member Undertaker's Avatar
    Join Date
    Nov 2000
    Location
    Earth
    Posts
    2,533
    Thanks
    39
    Thanked:        189
    Karma Level
    425

    Default Re: Desperate kids excel coursework need help with macro

    Code:
      
    
    Range("K24,N24,Q24").Select
        Range("Q24").Activate
        Selection.Copy
        'activate prices sheet
        Sheets("Prices").Activate
        ' get last row and paste
        lastrow = Range("A65536").End(xlUp).Row
    Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("add product page").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        Range("N24").Select
        ActiveCell.FormulaR1C1 = ""
        Range("K24").Select
        ActiveCell.FormulaR1C1 = ""
        Range("H24").Select

    that should help, activate prices sheet, find last row, then paste in last row +1

    Thanks to Undertaker

    Teajunkie (22nd October 2014) 


  9. #9
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,848
    Thanks
    1,034
    Thanked:        783
    Karma Level
    302

    Default Re: Desperate kids excel coursework need help with macro

    As Undertaker said you are selecting the sheet but not identifying where you want to paste and therefor it pastes where the cursor was last and that's why its over righting.

    Code:
         Sheets("prices").Select
        ActiveSheet.Paste
    So as either of us have show me with a function or Undertakers which is basically simulating a CRTL + UP from the last row in the workbook, which both will identify the next empty row and select it so that when you do the paste it is in the right position ;-)

    Thanks to akimba

    Teajunkie (22nd October 2014) 


Similar Threads

  1. [HELP] Excel Macro Help - Run macro using active sheet
    By Karoline in forum Programming
    Replies: 2
    Last Post: 11th December 2014, 04:36 PM
  2. Excel Macro basics
    By Gazzr in forum Programming
    Replies: 11
    Last Post: 3rd November 2011, 10:06 PM
  3. Excel Macro Prob
    By Hadfield82 in forum Programming
    Replies: 2
    Last Post: 27th January 2007, 08:33 PM
  4. a bitch about coursework
    By God is a DJ in forum The Comedy Club
    Replies: 6
    Last Post: 16th April 2003, 03:27 PM

Social Networking 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
  •