Help to create a macro

J

jaleel.k.mohammed

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
 
C

Claus Busch

Hi Jaleel,

Am Fri, 23 Jan 2015 22:20:16 -0800 (PST) schrieb
(e-mail address removed):
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.
 
J

jaleel.k.mohammed

Hi Jaleel,

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


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.


Dear Mr. Claus B,

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

Regards,

Jaleel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top