Hi,

I have some employee Records in sheets "Nov07","Dec07" and "Jan08".
the first two columns(A and B) are employee number and name in each sheet,
and column C is their overtime.

I want to create a consolidated sheet "YTD" so that I can see all their YTD
overtime in one sheet and do some analysis.

Hence the new sheet will have first two columns A and B same as above howver
column C, D and E will be the overtime from each sheet. It would have been a
simple copy paste if the number of employee had been static, but it changes
every months as new employee joins in or some leaves. Hence the macro needs
to validate employee number before copying the value from source sheet to
destination sheet

I have tried my best to create a Macro to do the same as below:

Dim Rno As Integer
Dim Svalue, OValue As String

Sub CopyCol()
For Each Tmprange In Worksheets("YTD").Range("b2:b117")
For Rno = 2 To 117
Svalue = "B" & Trim(Str(Rno)) 'cell reference of employee name from
Master "YTD" sheet
OValue = "C" & Trim(Str(Rno)) 'cell containing overtime of employee in
each sheet
If Trim(Tmprange.Value) = Trim(Worksheets("Oct07").Range(Svalue)) Then
Worksheets("YTD").Cells(Tmprange.Row, Tmprange.Column + 1).Value
= Trim(Worksheets("Oct07").Range(OValue))
End If
If Trim(Tmprange.Value) = Trim(Worksheets("Nov07").Range(Svalue)) Then
Worksheets("YTD").Cells(Tmprange.Row, Tmprange.Column + 2).Value
= Trim(Worksheets("Nov07").Range(OValue))
End If
If Trim(Tmprange.Value) = Trim(Worksheets("Dec07").Range(Svalue)) Then
Worksheets("YTD").Cells(Tmprange.Row, Tmprange.Column + 3).Value
= Trim(Worksheets("Nov07").Range(OValue))
End If
If Trim(Tmprange.Value) = Trim(Worksheets("Jan08").Range(Svalue)) Then
Worksheets("YTD").Cells(Tmprange.Row, Tmprange.Column + 4).Value
= Trim(Worksheets("Jan08").Range(OValue))
End If
Next Rno
Next
End sub

I know that this code looks non-generic and naive(I have a very basic
knowledge of VBA);

The problem you will see is that I will have to add a new statement every
time a new sheet is created.

Is there a better and efficient method or solution for this problem?