+ Reply to Thread
Results 1 to 7 of 7

VBA Cut and Paste if Criteria Exists

  1. #1
    Registered User
    Join Date
    11-28-2011
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    73

    VBA Cut and Paste if Criteria Exists

    There are about 20 columns of data with the rows sorted by values as the macro progresses. The button in the top left corner starts the macro and is in A1 next row is column headers and there are several blank rows inserted into the sheet to place the items in various groups. Here's my problem

    I have painted myself into a corner. When the macro was first created there were several Catalog# 6 in the source data (comes from different tabs). Because of the critera I have to use to sort the data into different catagories the last sort leaves the "6" at the top. My code below used to cut those 6's and insert them into the "Widget" section below. However there are now instances with customer data that contains no 6's so the code cuts rows 3 & 4 and places them into the "Widget" section of the sheet.

    I need to change this to either ignore if there are no 6's under Catalog# or loop the cut and past the "6" and loop it? I'm going to defer to the experts please.

    2019-07-24_9-47-40.jpg

    Please Login or Register  to view this content.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: VBA Cut and Paste if Criteria Exists

    Autofilter on Catalog Type column for "6" Then use SpecialCells(xlCellTypeVisible) to copy and insert the rows into the widget section.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    Re: VBA Cut and Paste if Criteria Exists

    copy the catalog type 6 to row group of widgets?

    try below, also attached sample file

    edit: the value "2" should be 6, just edit the code in the file and apply your data

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by k1dr0ck; 07-25-2019 at 05:32 AM.

  4. #4
    Registered User
    Join Date
    11-28-2011
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    73

    Re: VBA Cut and Paste if Criteria Exists

    k1dr0ck - Thanks for the input but it was only later that I realized I did not accurately portray the situation. In the updated image below I have described what "normally" occurs when the sorting is taking place. In the past there were no problems because the snippet of code above would cut rows 2,3,4,... and place them in the correct section "Widgets" but when the there is nothing above "Door Stops" it grabs the top 2 rows (header included) and inserts it into the body of the worksheet. So I need something in between. Something that will recognize IF there is data above the "Door Stops" to cut and paste below "Widgets" and if there is nothing than continue. If I'm not making sense then please let me know because I have too much time involved in sorting this out. Thanks Again!

    2019-07-25_12-34-47.jpg
    Last edited by joshag; 07-25-2019 at 01:40 PM.

  5. #5
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: VBA Cut and Paste if Criteria Exists

    Based on the sheet layout in Post #4. The row number for 'Catalog # should remain constant at 2. The row for 'Door Stops' would vary according to the number of Widgets accumulated. So:

    Please Login or Register  to view this content.
    Modified If statement on 27 Jul 2019
    Last edited by JLGWhiz; 07-27-2019 at 03:35 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    949

    Re: VBA Cut and Paste if Criteria Exists

    yeah based on your last post the code of JLGWhiz is suited for your problem

  7. #7
    Registered User
    Join Date
    11-28-2011
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    73

    Re: VBA Cut and Paste if Criteria Exists

    Sorry it took so long to get back to this but I had to put it down for a while. Part of a bigger project that has been making me a bit nuts. JLGWhiz - thank you. With a little tweaking this was definitely the "bones" I needed. Think it could have been a bit of confusion on my side with all of the name name changing I did. K1dr0ck gets a big thanks too for moving me in the right direction. Thank you both for taking the time to entertain me!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy paste to new workbook if new wb exists
    By prinsr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2019, 11:21 AM
  2. Copy and paste to new worksheet but overwrite if data already exists.
    By digimon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2015, 11:17 PM
  3. Replies: 9
    Last Post: 12-19-2014, 11:12 PM
  4. Replies: 5
    Last Post: 10-22-2013, 07:46 PM
  5. [SOLVED] Pop-up message box if a empty cell exists before copy & paste
    By XxCMoneyxX in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-09-2013, 11:49 AM
  6. Replies: 4
    Last Post: 10-25-2012, 05:09 PM
  7. Copy and paste value if it exists.
    By glocker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2009, 08:35 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1