Expert: Tom Ogilvy - 5/9/2008

Question
Sir

I am Dhandapani from Bangalore.  I have around 100 offices all over India.  Every month I am receiving the expenditure data from the 100 units for consolidation. I am entering each office data in different worksheets. The Monthly data  of one unit I am entering like this.

 A                    B       C     D     E      F   G  
                    April    May   Jun   Jul    Aug
1  PAY                500    100    50    300    200
2  D.A.               200    150    60    120    700
3  House Rent         120    450    90    900    800
4  CityAllowance      400    900    80    700    300

Now I have created one sheet "Consolidation".  This sheet contains the contents in column "A" and Month Column in "B"
Now if I select the month name the values in the corresponding month data should be updated in Consolidation worksheet.  eg.  If I selection "May"  The data in the May month should be updated in consolidation.


Kindly answer this question  by using VBA code Sir.

Dhandapani
Bangalore.  

Answer
You show "PAY" as being in row 1, but I assume it is in row 2 (actually cell A2) and the word "April" is in B1



I will guess that you want the labels in column A and the monthly data copied to the next blank row in consolidation

sub CopyData()
set r = ActiveCell
set d = worksheets("Consolidation").Cells( _
  rows.count,1).End(xlup)(2)
set r1 = r.parent.Range(r.offset(1,0), _
  r.parent.Cells(rows.count,r.column).end(xlup))
r1.copy d.offset(0,1)
set r1 = Intersect(columns(1),r1.EntireRow)
r1.copy d
End sub

If you want it to find the month in row 1 of consolidation, then where is the data to be placed.  Is it to be added to the existing data in sheet consolidation?  I will assume that and do a paste special  ADD

sub AddData()
set r = ActiveCell
set d = worksheets("Consolidation").rows(1).find(r.value)
set r1 = r.parent.Range(r.offset(1,0), _
  r.parent.Cells(rows.count,r.column).end(xlup))
r1.copy
d.offset(1,0).PasteSpecial Paste:=xlPasteValues, _
  Operation:=xlAdd
End sub

--
Regards,
Tom Ogilvy