about macro programming

[Varsha Kattekar]

hi friends,
i have a problem in excel regarding to macro. i want to copy a cell
(Eg. E1) from one workbook to another everyday at 2:30pm in continuous form(downward form) i.e the destination worksheet had dates so it should be copied as per the dates at 2:30 pm everyday. Hope u can solve it. please

 

[tactps]

Welcome:

Undtested, but:

1. Press Alt-F11 to open VBA
2. In the left pane, double-click "ThisWorkbook"
3. In the right pane, paste this in:

Private Sub Workbook_Open()
Application.OnTime (#2:30:00 PM#), macro1
End Sub

4. From the insert menu, choose module.
5. In the left pane, double-click "Module1"
6. In the right pane, paste this in:
Sub macro1()
[a1].Copy Destination:=[b65536].End(xlUp).Offset(1, 0)
End Sub

You may need to change the macro to test that it hasn't already been done and also that it posts in the right spot.

 

[Pekkavee]

I think it should be like this:

Application.OnTime (#2:30:00 PM#), "macro1"

Pekka

 

[tactps]

As I said, "untested" - thanks Pekkavee.

 

[Nimrod]

Public Sub ONE_THIS_JUST_ONCE()
    Application.OnTime (#2:30:00 PM#), "QuietlyCopyData"
End Sub

Public Sub QuietlyCopyData()

    ' hide excel
    Application.Visible = False
    
    
    'Get Value from Source workbook without opening it
     SourceValue = ExecuteExcel4Macro("'C:\[test.xls]Sheet1'!R1C1")
     
     'Place value in sheet1 of this workbook
     With ThisWorkbook.Sheets("Sheet1")
        NextRow = .Cells(65536, 1).End(xlUp).Row + 1
        .Cells(NextRow, 1).Value = SourceValue
     End If
     
     ' Close Target Workbook
     ThisWorkbook.Save
     ThisWorkbook.Close
     Application.Quit
 
End Sub