Hi Experts ,

I had created 4 input screens (worksheet type i.e A, B,C,D) , which populate
different required information into 3 "database" - another excel worksheets,
all residing in the same workbook.

Fields In Worksheet A
CIF, Client NAME, Investment Amt, Reward Pts

Fields In Worksheet B
CIF, Client NAME, Referred Member, Total AUA, Reward Pts

Fields In Worksheet C
CIF, Client NAME, Total AUA, Reward Pts

Fields In Worksheet D
CIF, Client NAME, Redemption, Reward Pts

I would want to design a macro to combine selected fields from the 4
worksheets to compute the Total Reward Pts that the client has achieved.

i.e I only want to copy CIF, Client Name , Reward Pts from all the 4
worksheets

Any advise ?

Here is some code I wrote

You need to put the word END at the botttom of Column a on the worksheet A - D

I asssumed
CIF in column A
Reward Points in column E

You can change the Const statements to agree with your worksheet layout


Sub abc()

Const CIF_StartCell = "A2"
Const REWARD_StartCell = "E2"
Const Master_CIF_StartCell = "A2"
Const Master_REWARD_StartCell = "B2"
Const MasterWorksheetName = "Master"
Const StartRow = 2


Summary_RowCount = StartRow 'Start putting data here

For Each MyWorksheet In ThisWorkbook.Worksheets
If StrComp("Master", MyWorksheet.Name) < 0 Then

SourceRowCount = StartRow

Do While
(StrComp(Worksheets(MyWorksheet.Name).Range(CIF_St artCell).Offset(rowOffset:=SourceRowCount - StartRow, columnOffset:=0), "END") < 0)


Worksheets(MyWorksheet.Name).Range(CIF_StartCell). Offset(rowOffset:=SourceRowCount - StartRow, columnOffset:=0).Copy _

Destination:=Worksheets(MasterWorksheetName).Range (Master_CIF_StartCell). _
Offset(rowOffset:=Summary_RowCount - StartRow, columnOffset:=0)


Worksheets(MyWorksheet.Name).Range(REWARD_StartCel l).Offset(rowOffset:=SourceRowCount - StartRow, columnOffset:=0).Copy _

Destination:=Worksheets(MasterWorksheetName).Range (Master_REWARD_StartCell). _
Offset(rowOffset:=Summary_RowCount - StartRow, columnOffset:=0)

Summary_RowCount = Summary_RowCount + 1
SourceRowCount = SourceRowCount + 1
Loop
End If

Next MyWorksheet


End Sub

"Soultek" wrote:

Hi Experts ,

I had created 4 input screens (worksheet type i.e A, B,C,D) , which populate
different required information into 3 "database" - another excel worksheets,
all residing in the same workbook.

Fields In Worksheet A
CIF, Client NAME, Investment Amt, Reward Pts

Fields In Worksheet B
CIF, Client NAME, Referred Member, Total AUA, Reward Pts

Fields In Worksheet C
CIF, Client NAME, Total AUA, Reward Pts

Fields In Worksheet D
CIF, Client NAME, Redemption, Reward Pts

I would want to design a macro to combine selected fields from the 4
worksheets to compute the Total Reward Pts that the client has achieved.

i.e I only want to copy CIF, Client Name , Reward Pts from all the 4
worksheets

Any advise ?