Altering this macro: Insert copy of line in same place on 2 sheets!

Hi pplz,
I have a situation where on Sheet 1 I ahve
(A) (B) (C)
Name: Surname Random hidden calcs
John Smith
Frank Smith
Sall Smith


The above is THE SAME on sheet 2 except Column C has different calcs.
If I want to add a NEW person, I ahve the below macro that you select the cell you want to add a row below, and it will copy the row AND copy the calculations from the above row which works well.

My issue is that I want it to do the same action on Sheet 2.

So if i click on Row 3 of sheet 1 and activate the macro, it will add a line below John smith on both sheet 1 AND sheet 2!

This is my current code (from another site)

Please Login or Register  to view this content.

I also want it to input Today() as the input in Column C (after the names) on Sheet 2 in the Newly added column!!!!

Finally I want a box to come up as a warning when the first press the "add new patient" button that warns to " Have you Selected patient to input BELOW" (YES/NO) - yes = continue, no = cancel running of macro.

What is a good book to read that can help me with all this too?? Ive seen "VBA for dummies" lol (but i just dont know the quality!!)

Thanks all

GLenn

bump any ideas?

Hi gangel

Try this:

Please Login or Register  to view this content.

Regards, Rob.

PS Not sure which book to read. I learned by trying to do things, much as you are, and trying to understand the code I found online.

One pointer, contrary to the way the Macro Recorder works, you don't need to select things to work on them. e.g.

Please Login or Register  to view this content.

Remove all the selection stuff and just string the important bits together...

Please Login or Register  to view this content.

I remember this as a bit of an epiphany!

Try typing "Learn VBA" in to Amazon, lots of advice from reviewers there.

Cheers, Rob.

great thanks again
My workesheets for work are coming along really WELL

appreicate all the help!

Ok with that script 2 things occur:
If i press NO (i havent selected patient to go below) it still continues to "How many rows should i add" even though it doesnt add them

2. Comes up with "subscript out of range" and it doesnt add a line on the 2nd sheet.
Ive attached the workbook!


As you can see from sheet 1 and sheet 2, the same names exist and i need a new line to be created in the right place in sheet 2 when sheet1 gets the line

Hi gangel

To solve the problems:-
1) Please change the line 4th line of code from If x = xlNo Then Exit Sub to:

Please Login or Register  to view this content.

Note the change from xlNo to vbNo. Apologies - my error.

2) Change the name of Sheet2 to to match your workbook sheet names. i.e. any Sheet2's become "Date Record".

Complete revised code below:

Please Login or Register  to view this content.

Ok so its now working well, with the one issue that on sheet 2 it was putting the date in the wrong column (should be col D) so i changed the Code and got it working.
Also, Col 1 , 2 and 3 need to have their formulas copied down with the new line, just like the whole row is copied in sheet 1. This brings peoples names from sheet 1 to sheet 2.
I really cant see in the code where copy and paste is (argggg need that vba book haha)

bump? this is the last thing ill annoy pplz about!

Also i just found a 2000 VBA book. is this stuff still relevant? (its my dads) so i might use that to learn from, or is lots of this stuff out dated now/.