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

[tecsbrain]

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.

[jaslake]

Hi tecsbrain

Try replacing your Module 2 Code with this. CTRL + i will fire the Code

Please Login or Register  to view this content.

[tecsbrain]

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.

[jaslake]

Hi tecsbrain

See if this does as required

Please Login or Register  to view this content.

[tecsbrain]

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!

[jaslake]

Hi tecsbrain

We're working with two different Files. My File Column A Formulas are

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"))

[tecsbrain]

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.

[jaslake]

Hi tecsbrain

Change the Column References as indicated

Please Login or Register  to view this content.

[tecsbrain]

You've made me a happy man. What can I do in return?

[jaslake]

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.