Desperate kids excel coursework need help with macro

Thread: Desperate kids excel coursework need help with macro

  1. Teajunkie's Avatar

    Teajunkie said:

    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: [View]
    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: [View]
      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!!!!
    My decal venture has taken over my dog tag business
    nice to be busy though.
    Instagram and twitter @mrteajunke.
     
  2. akimba's Avatar

    akimba said:

    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: [View]
    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: [View]
    Sheets("prices").Select
     Range("A" & CStr(LastRow("A", 2, "prices") + 1)).Select 
    ActiveSheet.Paste

    ;-)
     
  3. Teajunkie's Avatar

    Teajunkie said:

    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.
    My decal venture has taken over my dog tag business
    nice to be busy though.
    Instagram and twitter @mrteajunke.
     
  4. Undertaker's Avatar

    Undertaker said:

    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. Teajunkie's Avatar

    Teajunkie said:

    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.
    My decal venture has taken over my dog tag business
    nice to be busy though.
    Instagram and twitter @mrteajunke.
     
  6. Undertaker's Avatar

    Undertaker said:

    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. Teajunkie's Avatar

    Teajunkie said:

    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
    My decal venture has taken over my dog tag business
    nice to be busy though.
    Instagram and twitter @mrteajunke.
     
  8. Undertaker's Avatar

    Undertaker said:

    Default Re: Desperate kids excel coursework need help with macro

    Code: [View]
      
    
    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
     
  9. akimba's Avatar

    akimba said:

    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: [View]
         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 ;-)