+ Reply to Thread
Results 1 to 22 of 22

VBA to copy data using 'Yes' as a starting point

  1. #1
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    VBA to copy data using 'Yes' as a starting point

    Hi,

    I'm trying to write code which will only work if the cell contains the word 'Yes'

    So i want it to be;

    If the cell AT = Yes then copy range P4:AL and paste this to a sheet called 'New Position Template' and paste values into cell A2

    Any ideas?

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data using 'Yes' as a starting point

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Hi,

    I've had to make some changes to the code as the data has changed,

    I need to copy the data from columns, EG:EL as long as column EF = "Yes".

    The current code above brings an error;

    "Method of 'Range' of Object'_worksheet' Failed.

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data using 'Yes' as a starting point

    "Method of 'Range' of Object'_worksheet' Failed.
    I wrote it for you. In which line of the code shows this error?

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data using 'Yes' as a starting point

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Thank you,


    I'm still getting an error on this line;

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data using 'Yes' as a starting point

    "If the cell AT = Yes then copy range P4:AL and paste this to a sheet called 'New Position Template' and paste values into cell A2"

    Please Login or Register  to view this content.
    Do you have a sheet called New Position Template?

  8. #8
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Yes, the data is actually on a sheet called New Workings,

    the data to be copied over to the sheet New Position Template

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data using 'Yes' as a starting point

    Since you did not specified the source sheet name, I used active sheet. If you had run it from the active sheet ,it still work

    Please Login or Register  to view this content.
    INTO
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Please Login or Register  to view this content.
    This is what it now looks like, but i still have the same error?

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data using 'Yes' as a starting point

    Please Login or Register  to view this content.
    INTO
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Ah, have a different error now, same line but;

    "Pastespecial method of range class failed

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data using 'Yes' as a starting point

    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA to copy data using 'Yes' as a starting point

    Edit: Too slow, same code as AB33

  15. #15
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Thanks for your responses, it works perfectly.

    How would i add code to the macro to save a specfic tab of data to a new workbook and save based on a data in cell B10 on a sheet called 'Start'

  16. #16
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Bump - No response

  17. #17
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA to copy data using 'Yes' as a starting point

    You will need to adapt it to suit your needs but something like:
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Many thanks for this, works a treat, could we adapt it so it's asks the user where they would like to save it?

  19. #19
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA to copy data using 'Yes' as a starting point

    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Many thanks,

    If i wanted to close the new workbook after saving would i just add;

    Please Login or Register  to view this content.

  21. #21
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA to copy data using 'Yes' as a starting point

    It would need to be
    Please Login or Register  to view this content.
    I think

  22. #22
    Forum Contributor
    Join Date
    05-01-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    822

    Re: VBA to copy data using 'Yes' as a starting point

    Brilliant, thanks for your help.

    Much appreciated

+ 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