Option Explicit

Private Sub Worksheet_Activate()
'by Mike Fogleman Oct 9, 2005
Dim LRow As Long, Lrow2 As Long
Dim rng As Range, rng2 As Range, c As Range

'The next 4 lines deal with sheet "UAllocatedCalcs"
'because Excel assumes code to be run on the active sheet
'unless told differently. This sheet must be active for
'the code to run by definition of the Worksheet_Activate event.

LRow = Cells(Rows.Count, "A").End(xlUp).Row 'finds last row
'with data in col A
If LRow = 3 Then LRow = 4 'if only Header exists start on next row
Set rng = Range("A4:B" & LRow) 'define existing data range
rng.Cells.Clear 'clear the range for new data

'Here we tell Excel to run code on a sheet that is not active
'by using a reference to the worksheet. Again we find the Last
'row in col A and define the range of data to analyze starting
'at row 6.

Lrow2 = Worksheets("CRJournal").Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = Worksheets("CRJournal").Range("A6:A" & Lrow2)

'We now loop down rng2 (Col A)1 cell at a time and evaluate
'the second cell to the right (Offset)if it is Blank ("")
'If True, then it runs the code following THEN
'If False, then it will skip to End If and loop to next cell.
For Each c In rng2
If c.Offset(0, 2).Value = "" Then
'each time we add data to "UAllocatedCalcs" we need to find
'the last row again and add 1 for the next row.
LRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
'this makes "A next row" = the current cell in the loop
Range("A" & LRow).Value = c.Value
'this makes "B next row" = 1 cell to right of current cell in loop
Range("B" & LRow).Value = c.Offset(0, 1).Value
'formats for 2 decimal places
Range("B" & LRow).NumberFormat = "0.00"
End If
Next c 'loop back to line: For Each c In rng2
End Sub


This should fix the start positions and explain how it does it.
Watch for word wrapping when you paste the code, it should show up red.
Mike F
"mikeburg" wrote in
message ...

The VBA code looks great. That's what I need because we work this sheet
every day. However, I am so new to VBA I am having trouble
understanding it. I need to modify it to:

CRJournal's data starts on row A6

and

UAllocatedCalcs list needs to start on row A4

Can you help me make the modification & maybe comment the code to help
me learn from it? Thanks a million. mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=474481