Automatically copy down a column

[Anne]

I put this to the worksheet.functions in error. I need to do this with a
macro.
I am working with a spreadsheet exported from Quickbooks and I am trying to
prepare the sheet to import into Access.
The jobNo (Col A) comes from a header and I need to copy this field to each
row which has the employee work info.
A B C D
133 (1st job header)
Empl Date Hrs
Empl Date Hrs
Empl Date Hrs
Empl Date Hrs
133 (1st job footer)
144 (next job header )
Empl Date Hrs
Empl Date Hrs
144 (next job footer)
145 (next job header)
etc
The problem is, that each week the number of jobs varies, so there has to be
a stop value.
Any help is appreciated.
Anne

[Rob]

Ann,

Not sure whether this will help but if you add a column, say A and assuming
data starts at row 2, the following entered and copied down will return what
you need. You can then copy paste value to column B and delete column A.

=IF(LEN(B2)>0,B2,A1)

The above could be accomplished with VBA but I'm not too sure how. Rob

"Anne" <[email protected]> wrote in message
news:[email protected]...
>I put this to the worksheet.functions in error. I need to do this with a
>macro.
> I am working with a spreadsheet exported from Quickbooks and I am trying
> to
> prepare the sheet to import into Access.
> The jobNo (Col A) comes from a header and I need to copy this field to
> each
> row which has the employee work info.
> A B C D
> 133 (1st job header)
> Empl Date Hrs
> Empl Date Hrs
> Empl Date Hrs
> Empl Date Hrs
> 133 (1st job footer)
> 144 (next job header )
> Empl Date Hrs
> Empl Date Hrs
> 144 (next job footer)
> 145 (next job header)
> etc
> The problem is, that each week the number of jobs varies, so there has to
> be a stop value.
> Any help is appreciated.
> Anne
>
>
>

[Anne]

Again I have to thank this great access group at
http://www.utteraccess.com/forums
for the answer to my problem and I hope that this will help someone else.
The answer that worked for me:

Sub fill_Job_num()

'This macro written 2-4-05 by Paul Hannah of Excelsior Servies _
'for Anne and may be copied or edited however you like.
'The macro was written with these assuming, The text not to copy start with
"Total" and
'Only empty cells get copied to.

Dim strLastNM As String 'holds the value to be copied, changes when a new
one shows up
Dim lngLastRow As Long 'holds the last row number used by the sheet.
Dim curcel As Variant 'holds the position of the loop.
'get last row number
lngLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

'Start a Loop that will run form A2 to the last row in column A
For Each curcel In Range("a2:a" & lngLastRow)
If curcel <> "" And Left(curcel, 5) <> "Total" Then 'look for the next
job num
strLastNM = curcel 'Get the next job number
ElseIf curcel = "" Then 'if the cell is empty
curcel.Formula = strLastNM ' enter the last job number
End If
Next curcel
'loop back for the next cell
'that's it. Have fun.

End Sub

My pleasure to pass it on
Anne

"Rob" <[email protected]> wrote in message
news:[email protected]...
> Ann,
>
> Not sure whether this will help but if you add a column, say A and
> assuming data starts at row 2, the following entered and copied down will
> return what you need. You can then copy paste value to column B and
> delete column A.
>
> =IF(LEN(B2)>0,B2,A1)
>
> The above could be accomplished with VBA but I'm not too sure how. Rob
>
> "Anne" <[email protected]> wrote in message
> news:[email protected]...
>>I put this to the worksheet.functions in error. I need to do this with a
>>macro.
>> I am working with a spreadsheet exported from Quickbooks and I am trying
>> to
>> prepare the sheet to import into Access.
>> The jobNo (Col A) comes from a header and I need to copy this field to
>> each
>> row which has the employee work info.
>> A B C D
>> 133 (1st job header)
>> Empl Date Hrs
>> Empl Date Hrs
>> Empl Date Hrs
>> Empl Date Hrs
>> 133 (1st job footer)
>> 144 (next job header )
>> Empl Date Hrs
>> Empl Date Hrs
>> 144 (next job footer)
>> 145 (next job header)
>> etc
>> The problem is, that each week the number of jobs varies, so there has to
>> be a stop value.
>> Any help is appreciated.
>> Anne
>>
>>
>>
>
>