+ Reply to Thread
Results 1 to 16 of 16

Macro to cut and paste data if "yes" is in column

  1. 06-12-2017, 04:31 AM #1
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26

    Macro to cut and paste data if "yes" is in column

    Good Evening,

    I need some help with a macro please, I'm trying to cut data from rows in worksheet 1 when "yes" has been entered into column Q, then paste to next empty row in worksheet 2, but not having much idea of where to start.
    Reply With QuoteRegister To Reply
  2. 06-12-2017, 04:42 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,681

    Re: Macro to cut and paste data if "yes" is in column

    Record a macro while you filter column Q for yes. Select and cut the visible rows and paste them into next available row on sheet 2.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 06-12-2017, 07:52 AM #3
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to cut and paste data if "yes" is in column

    Maybe:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 06-12-2017, 11:18 PM #4
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26

    Re: Macro to cut and paste data if "yes" is in column

    Thanks for this John,

    When I try to run the macro I get "runtime error 9, subscript out of range?
    Reply With QuoteRegister To Reply
  5. 06-12-2017, 11:22 PM #5
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26

    Re: Macro to cut and paste data if "yes" is in column

    Thanks Trevor,

    When I try this the rows between the filtered "yes's" also get cut and pasted into sheet 2.

    My desired outcome would be for the filtered "yes" rows to be cut and pasted as soon as yes was entered into the column
    Reply With QuoteRegister To Reply
  6. 06-13-2017, 06:41 AM #6
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to cut and paste data if "yes" is in column

    Quote Originally Posted by hadleedog View Post
    Thanks for this John,

    When I try to run the macro I get "runtime error 9, subscript out of range?
    What's the name of your sheet?
    Reply With QuoteRegister To Reply
  7. 06-13-2017, 09:09 PM #7
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26
    Quote Originally Posted by JOHN H. DAVIS View Post
    What's the name of your sheet?
    The sheet is named "input data sheet " the paste sheet is named "consumed inventory "
    Reply With QuoteRegister To Reply
  8. 06-14-2017, 07:07 AM #8
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to cut and paste data if "yes" is in column

    Try:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 06-14-2017, 11:27 PM #9
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26

    Re: Macro to cut and paste data if "yes" is in column

    Tried that John, but now get
    Run-time error 1004
    Application-defined or object-defined error
    Reply With QuoteRegister To Reply
  10. 06-15-2017, 02:04 AM #10
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,681

    Re: Macro to cut and paste data if "yes" is in column

    Suggest you post a sample workbook with some typical data.
    Reply With QuoteRegister To Reply
  11. 06-15-2017, 03:10 AM #11
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26

    Re: Macro to cut and paste data if "yes" is in column

    I think the file attached ok.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 06-15-2017, 04:04 AM #12
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,912

    Re: Macro to cut and paste data if "yes" is in column

    Are you sure it's column Q we have to look at for filtering ?
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  13. 06-15-2017, 04:05 AM #13
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,681

    Re: Macro to cut and paste data if "yes" is in column

    Only had a quick look on my iPad, but the sheet name is different to what you said earlier.
    Reply With QuoteRegister To Reply
  14. 06-15-2017, 04:12 AM #14
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26

    Re: Macro to cut and paste data if "yes" is in column

    Sorry, column M on this sheet please
    Reply With QuoteRegister To Reply
  15. 06-15-2017, 04:17 AM #15
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26

    Re: Macro to cut and paste data if "yes" is in column

    Yes this sheet slightly altered but same issue, cut and paste row if m = yes
    Reply With QuoteRegister To Reply
  16. 06-15-2017, 04:23 AM #16
    hadleedog
    hadleedog is offline
    Registered User
    Join Date
    07-12-2014
    Location
    invercargill, new zealand
    MS-Off Ver
    2007
    Posts
    26

    Re: Macro to cut and paste data if "yes" is in column

    I'd just like to add that our thoughts are with you and all affected by the recent tragedies in your part of the world.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel Macro - Help with IF statement (IF column C = "VH*", paste "*" into column D, othe
    By michaelranderton in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-10-2017, 10:30 AM
  2. Display in a column of "sheet_01" all the data of any column of a "sheet_02". My "sheet_2"
    By Manuel Ferreira in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-20-2016, 01:59 AM
  3. [SOLVED] Macro - copy a range of cells - paste as "values" to column - then next column - etc.
    By MIGARDEIN in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2015, 07:32 PM
  4. Short "Basic" Macro to copy and paste formulas "N" times.
    By gradyhawks in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2014, 02:34 PM
  5. [SOLVED] Need to modify the Paste function of this VBA Macro from "Paste" to "Paste Special Values"
    By zicitron in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2013, 03:44 AM
  6. [SOLVED] Copy multiple rows that contain a certain "name" in column "A" then paste it into new work
    By rschlot2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2013, 02:25 PM
  7. Pivot Table - How to "add a data column" "of the same field" "across multiple sheets"
    By virsilens in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-06-2013, 12:45 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1