automating copy and paste
 

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

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.

--
Andy.


"Anne" wrote in message
...
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

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

On Fri, 4 Feb 2005 16:21:35 -0000, <Andy B wrote:

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.

--
Andy.


"Anne" wrote in message
...
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