+ Reply to Thread
Results 1 to 6 of 6

Copy a range of cells that contains formulae on one row to a new row

  1. #1
    Registered User
    Join Date
    05-05-2020
    Location
    Canada
    MS-Off Ver
    2003, 2013
    Posts
    4

    Copy a range of cells that contains formulae on one row to a new row

    Good day,

    I have the following query...please see attached sheet:

    Sample_A Sheet
    Row 1: Headers
    Row 2: Main data entry section
    Row 3: Contains references to Row 2
    * This row will be later protected.
    The key cell is cell D1
    When a number is entered in cell D1, say for example, 10
    1) Cells A3 to D3 are first copied, and then,
    2) These copied cells are then copied to Row 4 to Row 11
    * Thus, are repeated so the the number of copied rows equal the value of D2 - less two rows.

    Sample_B Sheet
    Contains sample data and perceived output.

    Thanks to all who may be of assistance.
    Attached Files Attached Files
    Last edited by mr-es335; 05-07-2020 at 09:35 AM.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Inventory Control Sheet

    Hi mr-es335,

    Welcome to the forum!!

    Some of your cell references are out but see how this goes:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    05-05-2020
    Location
    Canada
    MS-Off Ver
    2003, 2013
    Posts
    4

    Re: Inventory Control Sheet

    Robert,
    Firstly, thank you so very much for both the introduction and the sample code! Bot are very much appreciated!

    Secondly, I believe that I have corrected the cell references which you were so kind to appoint out...

    Thirdly, the sample code works almost as expected...please see attached sample sheet....

    For whatever reason, Column C is apparently not incrementing correctly.
    • Comparing Sample_A to Sample_B, the numbers in Column C should be incrementing sequentially.
    • Note: If I manually copy Row 3, then manually select Row 4 to Row 11, and finally past the Row 4 contents to that selected range, the copy works as expected.

    Thanks again for your assistance Robert.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-05-2020
    Location
    Canada
    MS-Off Ver
    2003, 2013
    Posts
    4

    Re: Inventory Control Sheet

    Good day,

    I must say, "This is one busy forum!!'

    I see this as:

    If D2=10 then
    copy cells A3:D3 to A4:D9 - Where the final value of D9 is determined by the value of D2.
    Endif

    C2 has the formula: =C2+1, C3=C3+1, C4+1, C5+1...and so on.


    I do hope that this all makes sense?

    Any further assistance with this posting would be greatly appreciated!
    Last edited by mr-es335; 05-06-2020 at 04:18 PM.

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Inventory Control Sheet

    I was trying to avoid copying to make the code as simple as possible but seems like you have to (only an extra line I suppose):

    Please Login or Register  to view this content.
    Regards,

    Robert

  6. #6
    Registered User
    Join Date
    05-05-2020
    Location
    Canada
    MS-Off Ver
    2003, 2013
    Posts
    4

    Re: Copy a range of cells that contains formulae on one row to a new row

    Robert,

    Thanks for this, Robert....very much appreciated...now if i can just get my head around what you did...I will be "laughin' "!!

+ 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. help building inventory control spread sheet
    By sand-297 in forum Excel General
    Replies: 3
    Last Post: 02-23-2016, 05:42 PM
  2. Using a barcode scanner and UPC master sheet for Inventory Control
    By jlucas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2011, 04:03 PM
  3. Inventory Control
    By Ola Sigurdh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 05:05 PM
  4. [SOLVED] Inventory Control
    By Glenn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  5. Inventory Control
    By Ola Sigurdh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 09:05 AM
  6. [SOLVED] Inventory Control
    By Glenn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] Inventory Control
    By Glenn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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