Sub copyandcleanup()'SalesAidSoftware

'make temp sheet and clean up
Application.ScreenUpdating = False
Sheets("LabourReport").Copy after:=ActiveSheet
With Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Columns("B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete

'delete destination sheet rows
With Sheets("PivotTableData")
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
If lr 0 Then .Rows(2).Resize(lr).Delete
End With

'copy data
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByColumns, xlPrevious).Column
Range("a2").Resize(lr, lc).Copy Sheets("pivottabledata").Range("a2")

'delete tempsheet
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

'restore updating
Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
What does the AFTER look like. If desired, send you file to my address
below along with a clear explanation and before/after examples. I will
take a look tomorrow.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joyce" wrote in message
...
Hi,

I am working with a report that is downloaded into Excel. The format
cannot
be changed, so I have to reformat in order to generate a pivot table.

Here is an example of the layout:
John Smith 54
Field 1 Field 2 Field 3 Field 4
Field 1 Field 2 Field 3 Field 4
Field 1 Field 2 Field 3 Field 4
Totals for John Smith

Mary Jones 88
Field 1 Field 2 Field 3 Field 4
Field 1 Field 2 Field 3 Field 4
Field 1 Field 2 Field 3 Field 4
Field 1 Field 2 Field 3 Field 4
Field 1 Field 2 Field 3 Field 4
Field 1 Field 2 Field 3 Field 4
Totals for Mary Jones

Etc.

The lines of data vary per employee and per report. I am creating a
macro
that will automate the process, but am encountering a problem with my
method.

I insert two headings to the right of Field 4 called EmployeeName and
EmployeeNo.

I then create a Vlookup that looks the combined information in the field
at
the top of each section.

It works fine, except that the references aren't relative, so if the
number
of rows change, the vlookup doesn't work.

Here is part of my code:

ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,Employees,2,0)"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C1,Employees,3,0)"
Range("P3:Q3").Select
Selection.AutoFill Destination:=Range("P3:Q13")
Range("P3:Q13").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A2").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Rows("14:14").Select
Selection.Delete Shift:=xlUp
Range("A16").Select
Selection.End(xlToRight).Select
Range("P16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,Employees,2,0)"
Range("Q16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,3,0)"
Range("Q16").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R15C1,Employees,3,0)"
Range("P16:Q16").Select
Selection.AutoFill Destination:=Range("P16:Q29")

I have to resolve the number of times this repeats as well, but would
like
to resolve this first!

Thanks for any assistance.