Make range dynamic

[pboltonchina]

Hi everyone and thanks for looking. I use the code below to copy a range and add it to the bottom of the copied range and then print it out. It;s basically a Fire sheet for work where column A has names in 2 sections. When I add or delete a name I have to change the offset in the macro to suit. I would like to make this dynamic so it detects any name changes itself and adjusts the range accordingly.

Sub AddOneWeek()
Application.ScreenUpdating = False
Application.EnableEvents = False
Cells(Rows.Count, "A").End(xlUp).Offset(-40).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy Destination:=Cells(Rows.Count, "A").End(xlUp).Offset(2)
Cells(Rows.Count, "A").End(xlUp).Offset(-40).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Rows.AutoFit
Cells(Rows.Count, "A").End(xlUp).Offset(-40).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).PrintOut Copies:=1, Collate:=True
Cells(Rows.Count, "A").End(xlUp).Offset(-40).Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

There are 2 sections to the copied data with the names duplicated.
First section

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
708	NAME	SAT	Fire	SUN	Fire	MON	Fire	TUES	Fire	WED	Fire	THUR	Fire	FRI	Fire
709	Name 1
710	Name 2
711	Name 3
712	Name 4
713	Name 5
714	Name 6
715	Name 7
716	Name 8
717	Name 9
718	Name 10
719	Name 11
720	Name 12
721	Name 13
722
723	Fire co-ordinators signiture									Date
724
725	Overtime request form for week ending:								07/05/2010
Checklist 2010

second section

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
726
727	Enter amount of overtime you are requesting
728
729		SAT	SUN	MON		TUE		WED		THUR		FRI		COMMENTS
730	NAME			PRE	POST	PRE	POST	PRE	POST	PRE	POST	PRE	POST
731	Name 1
732	Name 2
733	Name 3
734	Name 4
735	Name 5
736	Name 6
737	Name 7
738	Name 8
739	Name 9
740	Name 10
741	Name 11
742	Name 12
743	Name 13
744
745	Autorised by							Date						Site Manager
Checklist 2010

Regards
Paul

 

[pboltonchina]

Bump