Hello Friends,

I am new here and new to Excel Programming too. I have a issue.

In Employee1 workbook, I have many employee records with first name,
last name, title, ssn ......., year 1, amount 1, year 2, amount 2 ..
columns.

My issue is to copy these specific records from the above workbook and
paste it in a new workbook so that the year and amount are shown
individually for every employee.

Sample:

FName LName Title SSN Gender DOB Address1 .. .. Year1 Amount1 Year2.
Bourne Cyber Mr 123 M 1/1/01 jj 1991 20000 1992

I need the output format in a different file as

Fname Lname Title SSN Year1 Amount1
Fname Lname Title SSN Year2 Amount2
Fname Lname Tilte Ssn Year3 Amount3
Fname2 Lname2 Title2 SSN2 Year1 Amount1
Fname2 Lname2 Title2 SSN@ Year2 Amount2


This is just a sample , It may contain many records. Basically i need
to loop it.

Please Help.
Urgent.

Abhi

yuo can do this if you create a pivot table...might be easier for you than
writing VBA code.

"abhishek" wrote:


Hello Friends,

I am new here and new to Excel Programming too. I have a issue.

In Employee1 workbook, I have many employee records with first name,
last name, title, ssn ......., year 1, amount 1, year 2, amount 2 ..
columns.

My issue is to copy these specific records from the above workbook and
paste it in a new workbook so that the year and amount are shown
individually for every employee.

Sample:

FName LName Title SSN Gender DOB Address1 .. .. Year1 Amount1 Year2.
Bourne Cyber Mr 123 M 1/1/01 jj 1991 20000 1992

I need the output format in a different file as

Fname Lname Title SSN Year1 Amount1
Fname Lname Title SSN Year2 Amount2
Fname Lname Tilte Ssn Year3 Amount3
Fname2 Lname2 Title2 SSN2 Year1 Amount1
Fname2 Lname2 Title2 SSN@ Year2 Amount2


This is just a sample , It may contain many records. Basically i need
to loop it.

Please Help.
Urgent.

Abhi

try this:

Option Explicit
Sub Main()

Dim ThisRow As Long
Dim ThisCol As Long
Dim ThisCol14 As Long
Dim wsActive As Worksheet
Dim wsOut As Worksheet
Dim OutRow As Long

Set wsActive = ActiveSheet
Set wsOut = Worksheets.Add ' for output

'start loop at row 2 if row 1 is table headings
With wsActive
For ThisRow = 2 To .Range("A2").End(xlDown).Row
ThisCol = 8 ' first col of Year1
Do
OutRow = OutRow + 1
'get first 4 items
For ThisCol14 = 1 To 4
wsOut.Cells(OutRow, ThisCol14) = .Cells(ThisRow, ThisCol14)
Next
wsOut.Cells(OutRow, 5) = .Cells(ThisRow, ThisCol)
wsOut.Cells(OutRow, 6) = .Cells(ThisRow, ThisCol + 1)

ThisCol = ThisCol + 2
Loop Until .Cells(ThisRow, ThisCol) = ""

Next ' next record
End With

End Sub


adds a new sheet for the output
reads each row of the activesheet
places first four items of each row in the active sheet fro each pair of
year/amounts





"Patrick Molloy" wrote:

yuo can do this if you create a pivot table...might be easier for you than
writing VBA code.

"abhishek" wrote:


Hello Friends,

I am new here and new to Excel Programming too. I have a issue.

In Employee1 workbook, I have many employee records with first name,
last name, title, ssn ......., year 1, amount 1, year 2, amount 2 ..
columns.

My issue is to copy these specific records from the above workbook and
paste it in a new workbook so that the year and amount are shown
individually for every employee.

Sample:

FName LName Title SSN Gender DOB Address1 .. .. Year1 Amount1 Year2.
Bourne Cyber Mr 123 M 1/1/01 jj 1991 20000 1992

I need the output format in a different file as

Fname Lname Title SSN Year1 Amount1
Fname Lname Title SSN Year2 Amount2
Fname Lname Tilte Ssn Year3 Amount3
Fname2 Lname2 Title2 SSN2 Year1 Amount1
Fname2 Lname2 Title2 SSN@ Year2 Amount2


This is just a sample , It may contain many records. Basically i need
to loop it.

Please Help.
Urgent.

Abhi