In both places where I used Range("A1") I just used that cell as an anchor so
you'll need to replace a bit more code with your desired range. For instance
if you want to specifically designate a range to copy you would replace
"With Sheets(i).Range("A1").CurrentRegion"
with "With Sheets(i).Range("A2:F6")". You then also would not need to
offset and resize the region as I did to avoid copying the title row. So you
would delete
".Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)" which I have in the
next line of code.
Remember that when you hard code a range it will not expand as your
worksheet expands. You mentioned that each day's sheet had a varying number
of rows. This syntax would not accomodate that.
You can also work with named ranges. For instance if your worksheet has a
range named "MyRange" you can reference this range in code.
Range("MyRange").Select will select your named range.
In answer to your second question, paste this code between the last line of
code and the line which reads "End Sub".
Range(Range("F2"), Range("F2").End(xlDown)).Offset(0, 1).FormulaR1C1 _
= "=ROUNDUP((RC[-2]-RC[-3])*48,0)/2"
To avoid the line break problem you had earlier ensure that this pastes as
two lines of code which break at the " _".
This code measures the number of rows from F2 to the bottom of the list
then, in the same number of cells in the column to the right (column G),
enters the formula you gave me using the cell that is on the same row and two
columns back (column E) and the cell that is in the same row and three
columns back (column D).
If you want to learn more about writing macros and other handy Excel
features John Walkenbach's book Excel 2003 Power Programming with VBA is a
good one. It's best if read cover to cover. It's a big book, but an easy
read. Visit his site at
www.j-walk.com.
Let me know if you still have questions.
"Paul Cooke" wrote:
Hi Erin, I only just sorted that bit thanks for replying again, The code
works brilliantly !!
If you don't mind can i ask a few other questions...
If i wanted to select a specific range of cells would i just changed
the ("A1") bit to show the range?
and
If I wanted to add another column to the summary sheet to calulate the
time worked, can this be added to the code? I already have the code i
need for this purpose which is
=ROUNDUP((E3-D3)*48,0)/2
Please ignore the cell references in this bit as its currently used on
another workbook only
I hope you don'y mind me asking
Kind regards
Paul
--
Paul Cooke
------------------------------------------------------------------------
Paul Cooke's Profile: http://www.excelforum.com/member.php...o&userid=29268
View this thread: http://www.excelforum.com/showthread...hreadid=491675