PDA

View Full Version : How to have a macro run by itself



Neodubois
04-11-2007, 08:54 AM
I have created

Public Function Reformat()
Sheets("Bonds").Select
Range("A4:T30").Select
Selection.Copy
Sheets("Upload Bonds").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Currency").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Upload Currency").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Function


I would like this macro to run automatically as soon as a value has been updated in the original sheets or all the time as long as no button needs to be added

Many thanks in advance

Bob Phillips
04-11-2007, 12:15 PM
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Call Reformat
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Function Reformat()
Worksheets("Bonds").Range("A4:T30").Copy
Sheets("Upload Bonds").Select
Range("A2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Sheets("Currency").Cells.Copy
Sheets("Upload Currency").Select
Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Function


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Neodubois
04-12-2007, 01:41 AM
Many thanks

Does this mean I need to do this in each sheet

Bob Phillips
04-12-2007, 01:56 AM
Put the Reformat code in a general code module and add this to ThisWorkbook



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const WS_RANGE As String = "H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Sh.Range(WS_RANGE)) Is Nothing Then
With Target
Call Reformat
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Neodubois
04-12-2007, 02:27 AM
Thanks again will try this and let you know

Neodubois
04-12-2007, 02:37 AM
I get an error message saying he doesn't recognize the "Call Reformat " function

Bob Phillips
04-12-2007, 02:55 AM
Forgot to mention, make it Public as well.

Neodubois
04-12-2007, 03:08 AM
Changed them both to Public so in "ThisWorkbook" and in "Module1", and now nothing happens

Bob Phillips
04-12-2007, 03:17 AM
Post the workbook.

Neodubois
04-12-2007, 03:18 AM
This is in "ThisWorkbook"

Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const WS_RANGE As String = "A4" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Sh.Range(WS_RANGE)) Is Nothing Then
With Target
Call Reformat
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

And this in "Module 1"

Public Function Reformat()

Worksheets("Bonds").Range("A4:T30").Copy
Sheets("Upload Bonds").Select
Range("A2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Sheets("Currency").Cells.Copy
Sheets("Upload Currency").Select
Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Function

Bob Phillips
04-12-2007, 03:22 AM
Can you post the whole workbook, I don't have the worksheets that you do.

Neodubois
04-12-2007, 03:39 AM
No quite sure what you mean ?
All my print screens are to big to upload

But I did post he whole code from both

Bob Phillips
04-12-2007, 03:44 AM
I won't be able to see the problem because when I install it will work correctly for me. It is probably erroring in Reformat, but I cannot see that, or tell why, from a distance.

Neodubois
04-12-2007, 04:07 AM
I uploaded the print screen in a word doc

http://download.yousendit.com/16ECDE5E053352C6 (http://download.yousendit.com/16ECDE5E053352C6)
or
http://www.yousendit.com/download/QlVnc2ZBaFIwZ2swTVE9PQ (http://www.yousendit.com/download/QlVnc2ZBaFIwZ2swTVE9PQ)
Hope this helps

Neodubois
04-12-2007, 04:22 AM
OK Sorry it works now, my fault didn't adjust the range as I should have

My "original" sheets are formulas whih are updated when these are updated the macro doesn't react

Bob Phillips
04-12-2007, 04:39 AM
Look at OnTime in help, that does delayed execution.

Neodubois
04-13-2007, 03:13 AM
thanks it is working fine now.

I have the following macro which should also run by itself when the document is opened, do I also just paste it it "ThisWorkbook"?

Private Sub Workbook_Open()
Workbooks.Add
With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.AskToUpdateLinks = False
End Sub

mdmackillop
04-13-2007, 10:12 AM
FYI You can post a workbook using Manage Attachments in the Go Advanced section.

Neodubois
04-16-2007, 01:14 AM
I know, the problem was the size of my attachments

Thanks anyway

Bob Phillips
04-16-2007, 02:33 AM
You can post zip files.

Neodubois
04-16-2007, 03:49 AM
noticed that also, but that file was to big to