Help to create a macro

  • Thread starter Thread starter jaleel.k.mohammed
  • Start date Start date
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
 
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?
Click to expand...

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.
 
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.
Click to expand...


Dear Mr. Claus B,

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

Regards,

Jaleel
 
Back
Top