Dear MVPs,

I am using Excel 2007. I have an Excel file with 4 Sheets, namely Federal Bank, Canara Bank, ICICI Bank and Summary. In the Summary Sheet I have 4 Columns, in cell A1 Date, B1 Federal Bank, C1 Canara Bank and D1 ICICI Bank. I wish to enter todays date (24/01/2015) in cell A2 of Summary Sheet. There must be a button "UPDATE" in the Summary Sheet and when I press this button, the last figure in the column "H" of the three banks should appear under their names in B2, C2 and D2 respectively. Can anybody help to create such a macro?

Thanks in advance.

Regards,

Jaleel

Hi Jaleel,

Am Fri, 23 Jan 2015 22:20:16 -0800 (PST) schrieb
:

I am using Excel 2007. I have an Excel file with 4 Sheets, namely Federal Bank, Canara Bank, ICICI Bank and Summary. In the Summary Sheet I have 4 Columns, in cell A1 Date, B1 Federal Bank, C1 Canara Bank and D1 ICICI Bank. I wish to enter todays date (24/01/2015) in cell A2 of Summary Sheet. There must be a button "UPDATE" in the Summary Sheet and when I press this button, the last figure in the column "H" of the three banks should appear under their names in B2, C2 and D2 respectively. Can anybody help to create such a macro?

I am not a MVP but I answer you nevertheless.
Right click on sheet tab of sheet "Summary" = Show code and insert
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Dim i As Long
Dim shN As String

For i = 1 To 3
shN = Cells(1, i + 1)
Target.Offset(, i) = IIf(Len(Target) 0, _
Sheets(shN).Cells(Rows.Count, "H").End(xlUp), "")
Next
End Sub

You don't need a button. The values will be inserted when you write the
date in column A.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Saturday, January 24, 2015 at 1:50:23 PM UTC+5:30, Claus Busch wrote:
Hi Jaleel,

Am Fri, 23 Jan 2015 22:20:16 -0800 (PST) schrieb
:

I am using Excel 2007. I have an Excel file with 4 Sheets, namely Federal Bank, Canara Bank, ICICI Bank and Summary. In the Summary Sheet I have 4 Columns, in cell A1 Date, B1 Federal Bank, C1 Canara Bank and D1 ICICI Bank. I wish to enter todays date (24/01/2015) in cell A2 of Summary Sheet. There must be a button "UPDATE" in the Summary Sheet and when I press this button, the last figure in the column "H" of the three banks should appear under their names in B2, C2 and D2 respectively. Can anybody help to create such a macro?

I am not a MVP but I answer you nevertheless.
Right click on sheet tab of sheet "Summary" = Show code and insert
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Dim i As Long
Dim shN As String

For i = 1 To 3
shN = Cells(1, i + 1)
Target.Offset(, i) = IIf(Len(Target) 0, _
Sheets(shN).Cells(Rows.Count, "H").End(xlUp), "")
Next
End Sub

You don't need a button. The values will be inserted when you write the
date in column A.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


Dear Mr. Claus B,

That's wonderful. I tried it and it worked perfectly. Many thanks! You are great!

Regards,

Jaleel