automating copy and paste

[Anne]

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 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)

The problem is, that each week the number of jobs varies.
Any help is appreciated.
Anne

 

[Guest]

Hi

Try this:
Select the column / range. Then Edit / Go to / Special / Blanks. This will
select all of the blank cells in the range. Type = and hit the up arrow and
then type Ctrl Enter. To fix these values use Copy then Paste Special /
Values on the range.

 

[Gord Dibben]

Anne

To really automate it.......

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").column
Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With
End With
End Sub


Gord Dibben Excel MVP