How Do I Automatically Populate These Fields in XL2003?


New Member
Aug 6, 2010

System Info: OS - Windows XP, Excel Version - 2003

I am wanting to see if someone out there might be willing to guide me through the steps of doing something. It requires a bit of explanation and details, so bear with me.

GOAL: Transfer specific pieces of information from one xl sheet to a more "workable" format in another sheet.

Part of my job has me "grade" these tour reports. Grading consists of these things: transferring information about each tour (e.g. - Name of officer, tour number, docket unit used, and any errors) to various sheets that are used as summaries when the tours are audited. Grading also involves checking any errors against information detailed in each person personal shift report - which would indicate that the error should or could be "excused" (such as, for a time error due to a restroom break).

Currently, I manually input the information I need into a "notes" xl file - then I transfer that information to the various places it needs to go. I use this notes file because it is easier to keep track of this information in one place.

Here is an example of the tour report:
Example Tour Report in Google Docs
Some Notes:
  • some of the formatting doesn't transfer as well when uploaded to google docs, but for the sake of reference it should work alright
  • I have changed some of the information so that it doesn't reveal details about specific locations, business names, or employee names involved - places where changes have been made will have green text

Here is an example of the notes sheet I keep:
Example of Notes sheet in Google Docs

Here is where I specifically get the information I transfer - I will give you details based on the line it is on:
All these are in reference to the first sheet of the Example sheet
Line 13: I transfer the tour number - 3
Line 14: I transfer the start time (in military time) - 00:14
Line 14: I transfer the date - 7/29/10
Line 16: I transfer the employee first name into one column last name into another - First: Example ; Last: Name

This pattern would then continue at lines 68-72, lines 124-128, etc...

The things I just listed are what I would like to automate most.

Secondarily, I would also like the following to happen:
If you look at line 99 - you will see it is highlighted in yellow and that there is also a note in red in column E - this happens automatically whenever there is a time error (i.e. - time between two dockets is greater than 10m 59s). If I could - I would like to have these errors automatically called to may attention along with the other information I use for notes so that I can quickly locate the errors to see if they are excusable or not.

I don't know if this is doable, simple, difficult, or something else - so please let me know if I am asking "too much". I am very grateful that you have even read this far :) If you can offer any further help with this I would be very grateful for that as well.

Let me know if you need me to clarify anything - I tried to be as specific as I could - but this was kind of hard to put into words.

Thanks very much in advance!
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.


New Member
Aug 6, 2010
Try it out again, I made it so that you can edit it - so you should be able to double click to see what is in the boxes there. Unfortunately google docs doesn't overlap the text like excel does - but this should allow you to be able to see what is inside.

Thanks for taking a look :)


Well-known Member
Feb 17, 2003
I was able to expand the column and copy/paste to Excel.
Google docs only allows copying of 1000 cells, but that gave no problems.
Cannot see where Unit comes from.
'- Run from data sheet. Transfers to worksheet called "Summary"
'- Uses 'Find' to locate start of each block, checks for errors between.
'- Brian Baulsom August 2010
    Dim FromSheet As Worksheet
    Dim FromRow As Long
    Dim MyFind As String        ' find beginning of data
    Dim FoundCell As Range
    Dim Began As Variant
    Dim MyName As String
    Dim sp As Integer           ' space position in MyName
    Dim Tour As String
    Dim Unit
    Dim ErrorRow As Long        ' to check errors
    Dim ErrorCount As Integer
    Dim ErrorText As String
    Dim ErrorInfo As String
    Dim rw As Long
    Dim ToSheet As Worksheet
    Dim ToRow As Long
    MyFind = "TOUR No."
    Set FromSheet = ActiveSheet
    Set ToSheet = Worksheets("Summary")
    ToRow = 2
    ErrorRow = 2
    Application.Calculation = xlCalculationManual
    '- GET DATA
    Set FoundCell = FromSheet.Columns("A:A").Cells.Find(What:=MyFind)
    If Not FoundCell Is Nothing Then
        FirstAddress = FoundCell.Address
            FromRow = FoundCell.Row
            Application.StatusBar = "Processing row : " & FromRow
            With FromSheet
                '- DATE & TIME
                Began = .Cells(FromRow + 1, "A").Value
                Began = Right(Began, Len(Began) - 12)
                '- NAME
                MyName = .Cells(FromRow + 4, "A").Value
                MyName = Trim(Right(MyName, Len(MyName) - 23))
                sp = InStr(1, MyName, " ", vbTextCompare)   ' find space in name
                '- TOUR
                Tour = Trim(.Cells(FromRow, "A").Value)
                Tour = Right(Tour, Len(Tour) - InStr(1, Tour, "#", vbTextCompare))
            End With
            '- CHECK FOR ERRORS (COLUMN E)
            ErrorCount = 0
            ErrorInfo = ""
            If ErrorRow <> FromRow Then
                For rw = ErrorRow To FromRow
                    ErrorText = Trim(FromSheet.Cells(rw, "E").Value)
                    If ErrorText <> "" Then
                        ErrorCount = ErrorCount + 1
                        ErrorInfo = ErrorInfo & "[" & FromSheet.Cells(rw, "A").Value _
                            & ":" & ErrorText & "]"
                    End If
                ErrorRow = FromRow
            End If
            '- DATA TO SUMMARY
            With ToSheet
                .Cells(ToRow, "A").Value = DateValue(Began)
                .Cells(ToRow, "B").Value = TimeValue(Began)
                .Cells(ToRow, "C").Value = Left(MyName, sp - 1)
                .Cells(ToRow, "D").Value = Right(MyName, Len(MyName) - sp)
                .Cells(ToRow, "E").Value = Tour
                .Cells(ToRow, "G").Value = ErrorCount
                .Cells(ToRow, "H").Value = ErrorInfo
            End With
            '- NEXT DATA BLOCK
            ToRow = ToRow + 1
            Set FoundCell = FromSheet.Cells.FindNext(FoundCell)
        Loop While Not FoundCell Is Nothing _
            And FoundCell.Address <> FirstAddress
    End If
    With ToSheet
        .Range(.Cells(2, "A"), .Cells(ToRow, "A")).NumberFormat = "m/dd/yyyy"
        .Range(.Cells(2, "B"), .Cells(ToRow, "B")).NumberFormat = "hh:mm"
    End With
    MsgBox ("Done")
    Application.Calculation = xlCalculationAutomatic
End Sub


New Member
Aug 6, 2010
Hrm... it looks like I forgot to even post that part - sorry about that!

Here's where I get the unit number from:
Line 16 says: "RECORDER No. :84657"
The last two digits in that number are the unit number - in this case 57.

In the actual excel spreadsheet it has those last two digits highlighted red.

Don't know why I didn't do this before... but here is a google doc link where you can download the actual excel files - rather than the google modified ones.

Example Log:

Hopefully that is more helpful.

Thanks for the help!


Well-known Member
Feb 17, 2003
Presumably you have figured this out. Something like
                '- UNIT
                Unit = Trim(.Cells(FromRow + 3, "A").Value)
                Unit = Right(Unit, 2)
                ToSheet.Cells(ToRow, "F").Value = Unit

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back