Close

Results 1 to 9 of 9
  1. #1
    DF Admin Teajunkie's Avatar
    Join Date
    Dec 2009
    Location
    Devon
    Posts
    4,227
    Thanks
    1,827
    Thanked:        1,824
    Karma Level
    568

    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!
    model .zip

    PLEASE HELP!!!!
    Have you joined the DF discord server. https://discord.com/invite/YajVGQxDaw

  2. #2
    DF VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    368

    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 Admin Teajunkie's Avatar
    Join Date
    Dec 2009
    Location
    Devon
    Posts
    4,227
    Thanks
    1,827
    Thanked:        1,824
    Karma Level
    568

    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.
    Have you joined the DF discord server. https://discord.com/invite/YajVGQxDaw

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

    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 Admin Teajunkie's Avatar
    Join Date
    Dec 2009
    Location
    Devon
    Posts
    4,227
    Thanks
    1,827
    Thanked:        1,824
    Karma Level
    568

    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.
    Have you joined the DF discord server. https://discord.com/invite/YajVGQxDaw

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

    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 Admin Teajunkie's Avatar
    Join Date
    Dec 2009
    Location
    Devon
    Posts
    4,227
    Thanks
    1,827
    Thanked:        1,824
    Karma Level
    568

    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
    Have you joined the DF discord server. https://discord.com/invite/YajVGQxDaw

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

    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 VIP Member akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,846
    Thanks
    1,034
    Thanked:        783
    Karma Level
    368

    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. Mosley Is desperate to make it interesting
    By corskey in forum General Sports
    Replies: 4
    Last Post: 12th October 2002, 12:05 AM
  2. [REVIEW] Kids
    By mladen in forum Movie Talk
    Replies: 2
    Last Post: 11th October 2002, 03:09 PM
  3. Excel graph problem
    By marktb in forum PC Problems
    Replies: 2
    Last Post: 9th October 2002, 04:42 PM
  4. Kids T.V
    By BoredManc in forum Football
    Replies: 2
    Last Post: 29th September 2002, 02:25 PM
  5. Kids Education TV 70's/80's?
    By bugnote in forum TV Talk
    Replies: 4
    Last Post: 13th September 2002, 11:54 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
  •