You are here:

Excel/Update the values in the current month

Advertisement


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  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2012 About.com, a part of The New York Times Company. All rights reserved.