Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Save be added to this!
Can Cntrl-S be added to this script so each time it will save as well? Thanks for any help...Bob Private Sub Worksheet_Change(ByVal Target As Range) Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "dd/mmm/yy " 'dd/mm/yyyy ???? End With End With Me.Range("B162").Select End If End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Save be added to this!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "dd/mmm/yy " 'dd/mm/yyyy ???? End With End With Me.Range("B162").Select thisworkbook.Save '***** End If End Sub -- HTH... Jim Thomlinson "Bob" wrote: Can Cntrl-S be added to this script so each time it will save as well? Thanks for any help...Bob Private Sub Worksheet_Change(ByVal Target As Range) Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "dd/mmm/yy " 'dd/mm/yyyy ???? End With End With Me.Range("B162").Select End If End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Save be added to this!
Jim the name of the workbook is Cheques but I am getting a compile error on
Cheques.Save Thanks Bob "Jim Thomlinson" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "dd/mmm/yy " 'dd/mm/yyyy ???? End With End With Me.Range("B162").Select thisworkbook.Save '***** End If End Sub -- HTH... Jim Thomlinson "Bob" wrote: Can Cntrl-S be added to this script so each time it will save as well? Thanks for any help...Bob Private Sub Worksheet_Change(ByVal Target As Range) Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "dd/mmm/yy " 'dd/mm/yyyy ???? End With End With Me.Range("B162").Select End If End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can Save be added to this!
Oops sorry didn't actually need the name, fine now. Thanks
"Bob" wrote in message ... Jim the name of the workbook is Cheques but I am getting a compile error on Cheques.Save Thanks Bob "Jim Thomlinson" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "dd/mmm/yy " 'dd/mm/yyyy ???? End With End With Me.Range("B162").Select thisworkbook.Save '***** End If End Sub -- HTH... Jim Thomlinson "Bob" wrote: Can Cntrl-S be added to this script so each time it will save as well? Thanks for any help...Bob Private Sub Worksheet_Change(ByVal Target As Range) Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "dd/mmm/yy " 'dd/mm/yyyy ???? End With End With Me.Range("B162").Select End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I've added hyperlinks to cells, but when I save the file they don' | Excel Discussion (Misc queries) | |||
added up none numbers | Excel Worksheet Functions | |||
Added value to cell | Excel Discussion (Misc queries) | |||
How to save an excel spreadsheet as a text file without added quot | Excel Discussion (Misc queries) | |||
Calculating VAT (value added tax) | Excel Worksheet Functions |