How to have a macro run by itself

[Neodubois]

I have created

[VBA]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[/VBA]


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]

[vba]

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
[/vba]

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]

Many thanks

Does this mean I need to do this in each sheet

[Bob Phillips]

Put the Reformat code in a general code module and add this to ThisWorkbook

[vba]

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
[/vba]

[Neodubois]

Thanks again will try this and let you know

[Neodubois]

I get an error message saying he doesn't recognize the "Call Reformat " function

[Bob Phillips]

Forgot to mention, make it Public as well.

[Neodubois]

Changed them both to Public so in "ThisWorkbook" and in "Module1", and now nothing happens

[Bob Phillips]

Post the workbook.

[Neodubois]

This is in "ThisWorkbook"

[VBA]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[/VBA]

And this in "Module 1"

[VBA]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
[/VBA]

[Bob Phillips]

Can you post the whole workbook, I don't have the worksheets that you do.

[Neodubois]

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]

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]

I uploaded the print screen in a word doc

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

[Neodubois]

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]

Look at OnTime in help, that does delayed execution.

[Neodubois]

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"?

[VBA]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[/VBA]

[mdmackillop]

FYI You can post a workbook using Manage Attachments in the Go Advanced section.

[Neodubois]

I know, the problem was the size of my attachments

Thanks anyway

[Bob Phillips]

You can post zip files.