For others, cross posted: http://www.mrexcel.com/forum/excel-q...last-date.html
aakhan2011, some light reading: http://www.excelguru.ca/content.php?...-cross-posters
Now try these two macros:
Code:
Private Sub CommandButton3_Click() 'button labelled "Insert Automatically"
Dim z As Date
Set Source = ActiveSheet.Range("C3:C36")
Set mydata = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsx")
Set Destn = mydata.Sheets("SingleLine_Activation_Time ").Columns(1).Find(what:="Average", lookat:=xlPart, LookIn:=xlFormulas, searchformat:=False)
If Not Destn Is Nothing Then
z = Application.InputBox(Prompt:="Enter date")
Destn.EntireRow.Insert
Set Destn = Destn.Offset(-1)
Destn.Offset(, 1).Resize(, Source.Rows.Count).Value = Application.Transpose(Source.Value)
Destn.Value = z
mydata.Save
Else
MsgBox "Couldn't find a cell in column 1 of the destination sheet containing 'Average' above which to insert data."
End If
End Sub
Code:
Private Sub CommandButton4_Click() 'button labelled "Insert Date"
Dim z As Date
Set Source = ActiveSheet.Range("C3:C36")
Set mydata = Workbooks.Open(ThisWorkbook.Path & "\Sample.xlsx")
Set Destn = mydata.Sheets("SingleLine_Activation_Time ").Columns(1).Find(what:="Average", lookat:=xlPart, LookIn:=xlFormulas, searchformat:=False)
If Not Destn Is Nothing Then
Destn.EntireRow.Insert
Set Destn = Destn.Offset(-1)
Destn.Offset(, 1).Resize(, Source.Rows.Count).Value = Application.Transpose(Source.Value)
Destn.Value = Date
mydata.Save
Else
MsgBox "Couldn't find a cell in column 1 of the destination sheet containing 'Average' above which to insert data."
End If
End Sub
Both the above use the active sheet's range C3:C36 as source data which means you must have the source data sheet active when you run the macro. At the moment, that's a given as (a) the button triggers the macro and that button's on the active sheet and (b) the code is in the source data sheet's code module. However, if you hve multiple source sheets, at the moment you'd need to copy the code multiple times. Instead you could name the above macros differently (say blah1 and blah2, and have them in a standard code module (Insert, Module in the VBE)) then in the button_click event handler have it call blah1 or blah2:
Code:
Private Sub CommandButton3_Click() 'button labelled "Insert Automatically"
blah1
End Sub
Code:
Private Sub CommandButton4_Click() 'button labelled "Insert Date"
blah2
End Sub
Another way, is to have these two macros as blah1 and blah2 in a standard code module, but instead of using activeX buttons on the sheet as you have, use Form Controls buttons and assign the macro blah1 (or blah2) to them.
Bookmarks