+ Reply to Thread
Results 1 to 10 of 10

Prompt for # of new rows, copy formulas and conditional formatting

  1. 10-24-2014, 02:20 PM #1
    tecsbrain
    tecsbrain is offline
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    Prompt for # of new rows, copy formulas and conditional formatting

    Hi EF,

    Per the subject, I have a workbook where people will continuously enter data at the rate of about 100 rows per day. The first and last columns (A and K) contain formulas and/or conditional formatting.

    I found a macro that purports to do 80% of what I want:

    Please Login or Register  to view this content.
    But I installed and ran it and it crashes my worksheet ("Not enough system resources to display completely"). This is being done on a pretty high-end computer (16GB RAM, i7 processor) so I doubt it couldn't handle such a task.

    Also, this doesn't handle my conditional formatting requirement.

    I've attached the worksheet in question (it's a binary) if anyone wants to take a stab at it or a different approach.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-26-2014, 04:19 PM #2
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    Hi tecsbrain

    Try replacing your Module 2 Code with this. CTRL + i will fire the Code 
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
    Reply With QuoteRegister To Reply
  3. 11-06-2014, 06:48 AM #3
    tecsbrain
    tecsbrain is offline
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    Thanks a bunch jaslake. Only one issue remains, and it's more an issue of Excel behavior than the macro I think, and something I forgot to mention. Because I am entering about 150 daily records under the same date, I need this code to copy the same date all the way down. When I ran it, it filled the date incrementing by one day all the way down for the number of rows I asked for.

    Also, row B contains a formula that needs to be copied as well. Can you walk me through the code so I can learn how to modify it?

    Possibly an even better solution than hacking it to copy the existing date in the starting cell would be for it to prompt for both a date and a number of cells...but that's just a preference.

    Thanks a bunch -- why there isn't a "buy a drink" feature here, I don't understand.
    Last edited by tecsbrain; 11-06-2014 at 07:16 AM.
    Reply With QuoteRegister To Reply
  4. 11-06-2014, 12:08 PM #4
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    Hi tecsbrain

    See if this does as required 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 11-06-2014, 01:01 PM #5
    tecsbrain
    tecsbrain is offline
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    This almost works, except it still does the default Excel autofill (date increments by 1 each row) in column A and then # copies of the prompted date in column B. What I need (I wish I could understand enough VBA to make the change!) is for column A to contain the prompted # of rows of the entered date, and the formula in column B (which is =TEXT(DATE, "dddd")) copied down # times.

    Thanks so much for your help!
    Reply With QuoteRegister To Reply
  6. 11-06-2014, 03:40 PM #6
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    Hi tecsbrain

    We're working with two different Files. My File Column A Formulas are
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Please attach your File that reflects this
    the formula in column B (which is =TEXT(DATE, "dddd"))
    Last edited by jaslake; 11-06-2014 at 04:02 PM.
    Reply With QuoteRegister To Reply
  7. 11-06-2014, 04:23 PM #7
    tecsbrain
    tecsbrain is offline
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    Oh, goodness! I must have forgotten to update the file. I'm making you do more work than necessary, my bad. I have to continue adding to this file every day and I guess at some point I made a usability change that I subsequently forgot about.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 11-06-2014, 04:35 PM #8
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    Hi tecsbrain

    Change the Column References as indicated 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 11-06-2014, 04:54 PM #9
    tecsbrain
    tecsbrain is offline
    Registered User
    Join Date
    11-15-2010
    Location
    Arlington, VA
    MS-Off Ver
    Excel 365
    Posts
    34

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    You've made me a happy man. What can I do in return?
    Reply With QuoteRegister To Reply
  10. 11-06-2014, 05:05 PM #10
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Prompt for # of new rows, copy formulas and conditional formatting

    If you're happy I'm happy.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
    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] Copy Conditional Formatting to other rows
    By zoktolk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-15-2013, 01:49 PM
  2. Copy Conditional Formatting to other rows
    By Regan_Mitchell in forum Excel General
    Replies: 5
    Last Post: 10-15-2013, 08:19 AM
  3. Excel 2007 : Help inserting rows with correct formulas and conditional formatting
    By jenniferp in forum Excel General
    Replies: 2
    Last Post: 02-07-2012, 05:33 AM
  4. Excel 2007 : How to copy conditional formatting to all rows
    By jupster in forum Excel General
    Replies: 2
    Last Post: 06-03-2010, 01:04 AM
  5. Conditional Formatting Copy down Rows
    By SMac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-30-2005, 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