Hi Peter
the problem i'm struggling with is that i can't figure out how to tell excel
that you've entered a value in the "last" blank cell of a section. I'm
thinking along the lines of a worksheet_change event and dynamic named
ranges where when the sheet is opened the number of cells in each range is
calculated and then if the range size changes (ie you enter another value
in) then the copy & paste event happens. Sounds great, but then we have the
problem of storing the range sizes somewhere as once code terminates the
variables loose their "memory" - are you happy to have a hidden 'junk' sheet
to store this information on?- that's if someone else reading this post
doesn't have a better idea.
Cheers
JulieD
"Peter Long" wrote in message
...
I wanted a new line inserted if ANY of the values are entered
under the last blank rows under Family or friends. (Added to EACH day)
Because of the formulas on each day are different, and could also
be different for Friends and family, I would want to copy the Cells
above it on each sheet independently.
On DAY1 - Entering 2 on C5
DAY1 = Insert Blank Row at ROW6 (copied from DAY1 Row5)
DAY2 = Insert Blank Row at ROW6 (copied from DAY2 Row5)
DAY3 = Insert Blank Row at ROW6 (copied from DAY3 Row5)
On DAY1 - Entering 6 on D8
DAY1 = Insert Blank Row at ROW9 (copied from DAY1 Row8)
DAY2 = Insert Blank Row at ROW9 (copied from DAY2 Row8)
DAY3 = Insert Blank Row at ROW9 (copied from DAY3 Row8)
This way I could have different Formulas and Cell formats on each
DAY and for each section of Friends and family copied. I would want
this to continue endlessly. (I hope this explains well enough what I
am trying to do..)
I am actually working on 3 projects, each requiring this type of
line coping and inserting. This has me stumped on all three :-< If I
can get it working on this sample spread sheet, I can adapt it work on
all three.
On Sun, 31 Oct 2004 19:52:46 +0800, "JulieD"
wrote:
Hi Peter
to use the code, right mouse click on a sheet tab and choose view code
on the left of the VBE window you should see the project explorer with
your
workbook name in bold (if you can't see this choose view / project
explorer)
then under this you should see Sheet1, Sheet2, Sheet3, ThisWorkbook ... as
this code is run via a button it needs to go in a module sheet - ensure
that
you're clicked on your workbook name on the left and choose insert /
module
.. this will appear under ThisWorkbook as module1 ... double click on it
and
a white sheet of paper should be displayed on the right, copy & paste the
code into there.
as for having the code "autorun" based on some field's contents - this can
be done ... what field & what value do you want to "initiate" the code?
Cheers
JulieD
"Peter Long" wrote in message
. ..
Thanks for the effort Julie, I would perfer to not use a button to
more rows, but rather use some type of value check on input row to
auto add rows.
You efforts have not been wasted though, I will hold on to your
code and review it step by step for future refrence. ( I am pretty new
to Excel proggraming, but have done quite a bit of C++ and pascal
programing) I am eager to learn more....where would the code you send
normaly be put? In the code panel (VBA)
On Fri, 29 Oct 2004 17:20:36 +0800, "JulieD"
wrote:
Hi Peter
based on your simple example here's some code (which could probably be
cleaned up a bit but i've left it rather longwinded in case you need to
edit
it) which when assigned to a button on the form adds the new lines to
each
sheet then prompts for the name & number of each type of fruit for the
starting day. You could name the worksheet button "ADD NEW PERSON" or
similar
Sub AddNewRecord()
Rows("3:5").Select
Selection.Copy
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, 0).Select
Selection.End(xlToLeft).Select
Sheets(Array("Day1", "Day2", "Day3")).Select
Sheets("Day1").Activate
ActiveSheet.Paste
Selection.End(xlToLeft).Select
ActiveCell.Offset(2, 1).Select
ActiveCell.Value = InputBox("Enter name", "Enter name")
Sheets("Day2").Select
Sheets("Day1").Select
ActiveCell.Offset(0, 1) = InputBox("Enter number of apples.",
"Apples")
ActiveCell.Offset(0, 2) = InputBox("Enter number of oranges.",
"Oranges")
ActiveCell.Offset(0, 3) = InputBox("Enter number of grapes.",
"Grapes")
Application.CutCopyMode = False
End Sub
Please post back if you need assistance getting the code in the right
place
or linked to the button or talking about something other than fruit!
Hope this helps
Cheers
JulieD
"Peter Long" wrote in message
m...
I have had some trouble on a project I have been working on for
some time. I have tried numerous approaches, each with undesired
results. (VAB Code)
The project I am working on has become quite complex, so rather
than bother you with my spaghetti code, I have made a simple example
of what I am trying to do.
http://www.geocities.com/rockytophubby\sample.html
The sheet tracks People and how many fruits they had each day.
Day1 is where the information is Inputted, Day2 will double the DAY1
numbers, and Day three will triple the DAY2 Numbers.
The challenge I am faced with: On DAY1 every time ANY information
is inputted (even if there is no name) have a new Row AUTO inserted
for the next person on ALL three days. (Keeping in mind each day has
different formulas, so the Formatting and Formulas must be copied from
the line above it on each sheet.)
ANY help I can get here would be greatly appreciated, as I know
this is no task for a beginner...