+ Reply to Thread
Results 1 to 13 of 13

How to get my Macro to perform on a different row

  1. #1
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    How to get my Macro to perform on a different row

    Hello, I have probably worded the title wrong but I will try my best to explain what I want to do below.

    Basically, the macro has been created to move the data from columns I and J to columns F and G after payment has been received so that it goes round the same cycle each time, and it also removed the Y from column H. You should be able to see this in the code below, the macro has been put in a button where you see the "Click" text, however, I need to be able to do this for each row, is there a way I could use one button and choose the row I want to change each time instead of have the 'Click' on each row.

    Or if this isnt possible is there a way so that instead of me manually editing the ranges and copying it down each row, i could place it in a cell and drag it down like I can with formulas.

    I hope you understand what Im trying to say, thanks in advance for any help given. And if you do post any code could you please explain it as I am very new to this thanks.

    Here is an image of the excel spreadsheet:
    \1

    Here is the Macro Code:
    Please Login or Register  to view this content.
    UPADATE 1:

    Chippy provided me with the following which is exactly what I am after except one issue, it doesnt perform the paste special and seems to removed the formulae from the 2 columns thanks, I have managed to put this in a module, so I just need this final tweak:

    Quote Originally Posted by Chippy View Post
    Have the button call this routine instead of your code:
    Please Login or Register  to view this content.
    Put the code in a module (not a sheet nor workbook module).
    UPDATE 2:
    Its not the paste special that is the issue it removes the formulas from columns I and J Here are the before and afters:

    Attachment 161220

    Attachment 161221

    UPDATE 3:
    Quote Originally Posted by Chippy View Post
    Your PasteSpecial is just pasting values (Paste:=xlPasteValues) which is the same as Range("F2").Value = Range("I2").Value.

    Try this instead:
    Please Login or Register  to view this content.
    Quote Originally Posted by Purgatory View Post
    Unfortunately that did the following (see file), i.e. seemed to remmove it all so the previous one worked better, thank-you for the help given so far, it is much appreciated.

    Attachment 161236

    The values was still working it was just the formulas in I and J in the previous code, sorry If i am misunderstanding what you are trying to say.
    UPDATE 4:

    Quote Originally Posted by tigeravatar View Post
    Chippy's code doesn't use the copy/paste, it uses range.value = range.value, so yes, it's only getting the values and not formulas or formatting.
    As for the deletion of the I and J cells, just delete that line (it's this one):
    Please Login or Register  to view this content.
    forgot to try this and turns out its completely what I was after, thanks bud and thanks to chippy to for the original code :D

    SOLUTION

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by Purgatory; 06-11-2012 at 07:30 PM.

  2. #2
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    Re: How to get my Macro to perform on a different row

    Any help would be much appreciated

  3. #3
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    Re: How to get my Macro to perform on a different row

    Hello? ..

  4. #4
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: How to get my Macro to perform on a different row

    Quote Originally Posted by Purgatory View Post
    is there a way I could use one button and choose the row I want to change each time instead of have the 'Click' on each row.
    Have the button call this routine instead of your code:
    Please Login or Register  to view this content.
    Put the code in a module (not a sheet nor workbook module).
    Post responsibly. Search for excelforum.com

  5. #5
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    Re: How to get my Macro to perform on a different row

    I have a look and see if I can find out how to do that or do you have a tutorial, does this have the paste special bit in it?

  6. #6
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    Re: How to get my Macro to perform on a different row

    I dont understand the module part after doing the research however it does the required task using normal macro except it doesnt use the paste special as required.

  7. #7
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    Re: How to get my Macro to perform on a different row

    Anyone able to help updated the OP.

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: How to get my Macro to perform on a different row

    Chippy's code doesn't use the copy/paste, it uses range.value = range.value, so yes, it's only getting the values and not formulas or formatting.
    As for the deletion of the I and J cells, just delete that line (it's this one):
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    Re: How to get my Macro to perform on a different row

    Ill try that, but i realised the formatting has remained ok, its just that it deletes the formulas from those fields so it wont work the next time around.

  10. #10
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: How to get my Macro to perform on a different row

    Your PasteSpecial is just pasting values (Paste:=xlPasteValues) which is the same as Range("F2").Value = Range("I2").Value.

    Try this instead:
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    Re: How to get my Macro to perform on a different row

    Unfortunately that did the following (see file), i.e. seemed to remmove it all so the previous one worked better, thank-you for the help given so far, it is much appreciated.

    After 2 Macro Payment Plans.xlsm

    The values was still working it was just the formulas in I and J in the previous code, sorry If i am misunderstanding what you are trying to say.

  12. #12
    Registered User
    Join Date
    06-11-2012
    Location
    Farnborough
    MS-Off Ver
    Office 2010/2007
    Posts
    14

    Re: How to get my Macro to perform on a different row

    Quote Originally Posted by tigeravatar View Post
    Chippy's code doesn't use the copy/paste, it uses range.value = range.value, so yes, it's only getting the values and not formulas or formatting.
    As for the deletion of the I and J cells, just delete that line (it's this one):
    Please Login or Register  to view this content.
    forgot to try this and turns out its completely what I was after, thanks bud and thanks to chippy to for the original code :D

  13. #13
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: How to get my Macro to perform on a different row

    I think I've got it. What you didn't say and what your macro didn't show is that firstly 'Y' has to be entered in the H cell, which triggers data to appear in the I and J cells via formulas in those cells. The instructions on the sheet make this clearer:

    On payment enter Y in column H then copy columns I and J
    (paste special as values) into columns F and G and delete the
    Y in column H.

    Try this code, which does all the above steps:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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