+ Reply to Thread
Results 1 to 6 of 6

Copy down formulas based on number in a cell.

  1. #1
    Registered User
    Join Date
    02-24-2018
    Location
    Charlotte, NC
    MS-Off Ver
    2016
    Posts
    35

    Copy down formulas based on number in a cell.

    I've attached a simple example of what I am trying to do.

    I want to copy down formulas in the "Buyer (Example)" tab so that it shows all the items on a list in the MRP tab. (It seems redundant but in the real example it make sense to have a second tab.)
    I need to do this using the last number in they "Buy Column" on the MRP tab. The number is 17. I need to copy down the formulas in the "Buyer (Example) tab down 16 rows (17 minus the 1 row I already have.)

    The number of items on the "MRP" tab is random every day. It could be 17 items today but 25 items tomorrow.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    A demonstration as a starter :

    PHP Code: 
    Sub Demo1()
        
    Worksheets("MRP").UsedRange.Columns("A:B").Offset(1).Copy ['Buyer (Example)'!A6]
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Same player shoot again !


    Activating the Macro Recorder and operating manually : you can get your own base of code …

    If really formulas are needed :

    PHP Code: 
    Sub Demo2()
        
    Range("'Buyer (Example)'!A6:B" Worksheets("MRP").Cells(Rows.Count1).End(xlUp).Row).Formula "=MRP!A6"
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  4. #4
    Registered User
    Join Date
    02-24-2018
    Location
    Charlotte, NC
    MS-Off Ver
    2016
    Posts
    35

    Re: Copy down formulas based on number in a cell.

    Not quite but that's my fault for not being clear and giving a bad example. I've attached an updated file.

    The last number in column C of the MRP tab is 5. I want the rows on the "Buyer (Example" tab copied down 5 rows. You could also use something with max in that column because the last number will always be the largest.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,536

    Re: Copy down formulas based on number in a cell.

    Are you trying to limit the display to a certain number of items and their corresponding quantity? If so populate 'Column 1' using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Populate the 'Quantity' column using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    These formulas are modeled in the range A6:B15 on the Buyer(Example) sheet.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Copy down formulas based on number in a cell.

    Give this a whirl.
    Please Login or Register  to view this content.
    Last edited by bakerman2; 05-18-2018 at 10:31 PM. Reason: Finetuned.
    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 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: 2
    Last Post: 10-16-2017, 11:39 AM
  2. copy down formulas on serveral sheets based on the number of rows on sheet1
    By San75 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-28-2016, 08:09 PM
  3. [SOLVED] Formulas to enter Week Number, Weekday, and Time based on another cell being filled in
    By ConnorLally in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2015, 06:16 AM
  4. Insert a number of rows based on a cell value and copy formulas on multiple worksheets
    By Charleneq40 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2012, 12:45 PM
  5. [SOLVED] Excel VBA/Macro - Copy Number of Rows based on the number in a cell to another sheet
    By diane.alexander58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2012, 11:32 AM
  6. How to copy from a cell based on number
    By gs30 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2008, 12:03 AM
  7. Enter formulas in a certain number of rows, based on a cell value
    By jeffbert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2006, 10:10 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