+ Reply to Thread
Results 1 to 3 of 3

Complex Copy Paste Delete With Various Rules

  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

    Complex Copy Paste Delete With Various Rules

    Hi,

    I wondered if anyone could help with the attached. I have attached the sheet to make this clearer.

    Example 1: Prod No 2014109 Has Run Out In Sub Location 3 (104B)
    Define the Prod No - 2014109
    Define The Location - LOCATION1
    Define the Values in Sub Loc. 1 / Sub Loc. 2 / Sub Loc. 3 & Sub Loc 4
    If the Value Of Sub Loc. 1 / Sub Loc. 2 / Sub Loc. 3 or Sub Loc. 4 = 104B Go to that cell and delete the cell contents
    Go to the Sheet Location1
    Search Column A for the Sub Loc 2 (104A)
    When it is found Delete the cell contents in the cell to the right of it (201409)
    That deals with running out of a product in one location.

    Example 2: I also need to allow for running out of the products in all locations
    After running the above Macro
    Come Back to this sheet and Check the Values in Sub Loc. 1 / Sub Loc. 2 / Sub Loc. 3 & Sub Loc 4
    If they are all empty then Add a row in row 500 to maintain worksheet size
    Then copy the entire row on this sheet to the next blank row on the Out Of Stock Sheet as Values
    Then delete the entire row on this sheet
    Attached Files Attached Files

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Complex Copy Paste Delete With Various Rules

    Quote Originally Posted by timbo1957 View Post
    Example 1: Prod No 2014109 Has Run Out In Sub Location 3 (104B)
    Is that something you will know externally? How will we pass this value to the code? The current selected Cell?

    If the Value Of Sub Loc. 1 / Sub Loc. 2 / Sub Loc. 3 or Sub Loc. 4 = 104B Go to that cell and delete the cell contents
    Go to the Sheet Location1 & Search Column A for the Sub Loc 2 (104A)
    And Why 104A? Or did you mean 104B?

    Example 2: If they are all empty then Add a row in row 500 to maintain worksheet size
    Will this row be blank? I guess I'm not understanding, "to maintain worksheet size". Is this a table?
    David
    (*) Reputation points appreciated.

  3. #3
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

    Re: Complex Copy Paste Delete With Various Rules

    Hi Tinbendr,

    Thanks for posting back. I have done a bit more work on this since posting which might make things easier.

    On another sheet I have put an input box whete the following have defined names -

    My_Prod will = 2014109
    My_Location = Location1
    My_ SubLoc1
    My_ SubLoc2
    My_ SubLoc3 = 104B
    My_ SubLoc4
    My_Stock_Level OK

    The user can use these to define what they need the macro to do.

+ 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. Replies: 1
    Last Post: 12-18-2013, 04:13 PM
  2. Copy and paste from one tab to another with certain rules
    By missexcel12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2013, 07:25 AM
  3. Complex Copy and Paste Function
    By DewayAuggy in forum Excel General
    Replies: 2
    Last Post: 02-27-2012, 06:36 PM
  4. cannot copy or delete due to complex
    By reedzhou in forum Excel General
    Replies: 1
    Last Post: 07-11-2011, 03:06 AM
  5. [SOLVED] Tool / Code to copy and paste only the conditional formatting rules
    By Andibevan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-09-2006, 07:04 AM

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