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.

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.

Hi Don,

That is so very kind of you.

I will do so.

Thanks!

"Don Guillett" wrote:

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.