Posts by Neale

    Depends how you copy them.

    If you copy / paste the range then the print setting won't go with it. If you copy the sheet itself then the print setting go with it.

    Try recording a macro that copies a sheet to another sheet to find the code to do it.


    Welcome to the forum

    As Thomach has said the solution will depend on your data structure.

    If your data is in a table/list format then you could use the VLOOKUP function or even the Data functions with Data Validation to select the jobs.

    A macro might be used to cycle thru all the jobs and print off the graphs for each.

    more info will be needed before a solution can be suggested/provided.


    This site says

    If you have an investment that requires and produces a number of cash flows over time, the internal rate of return is defined to be the discount rate that makes the net present value of those cash flows equal to zero.
    End Quote

    As far as property is concerned you need to adjust your cash flows for the factors you listed - that is an art not a science - that's where risk comes into it (your guess is as good as anyones). The idea is that you compare prospective properties using each ones IRR.

    But remember that each property is unique and has its own risks eg the vacancy rate might be low but maybe your tenants are slow payers or they may trash the joint. - Take a look at insurance to see if it can reduce some of your risks.

    Tax also plays an important part. Check out re-financing after five years if you have capital gains tax that way you can get money out without paying tax - legally.

    Get lots of advice before getting into any investment


    Well I'm even later but WELL DONE! 1000 posts is a hell of lot of effort and your posts are a high quality.

    This forum is excellent because of the patience, knowledge and generosity of its members.

    Your efforts are very much appreciated.

    Ok state of play now

    i took out opening another file and put the "summary' sheet in the TotalCostsData file. So no other file need to be opened to save TotalCostsData (the book with the Before Save macro)

    Now i run the macro that opens TotalCostsData and then I clear the contents of the "summary" sheet then I save TotalCostsData - suddenly nothing happens! the "summary" sheet remains blank and stays that way after the TotalCostsData sheet is closed.

    When you step thru the code all the commands in the Before Save macro supposedly are done - but they are not performed????????????????????

    Any help greatly appreciated - I'm just about ready to ignore the Before Save macro and run the code from the external file each time I open the TotalCostsData file.

    Heres the code now

    The Before Save code in TotalCostsData

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wBook As Workbook, wSheet As Worksheet, wBook2 As Workbook
    Dim rRange As Range
    'Application.Calculation = xlCalculationManual
    'this macro creates a summary of the All Accoutns sheet
    'to be used to provide data to the input files

    Set wBook = Workbooks("TotalCostsData.xls")
    Set wSheet = wBook.Sheets("All Accounts")


    With wBook.Sheets("summary")
    .Cells(1, 1).PasteSpecial Paste:=xlValues
    Set rRange = .Range(.Range("A6"), .Range("Q65536").End(xlUp))
    rRange.AutoFilter _
    Field:=17, Criteria1:="=0", Operator:=xlOr, Criteria2:="="
    .AutoFilterMode = False
    rRange.Sort Key1:=.Range("q6"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With

    Application.Calculation = xlCalculationAutomatic

    End Sub

    The external macro opening Toital Costs data

    Sub Update3()

    Dim Wbook
    Application.EnableEvents = True

    Set Wbook = Workbooks.Open(Filename:= _
    "C:\Admin Managers\Copy of Budget2004\TotalCostsData.xls", UpdateLinks:=3)

    'clear the contents to check to see if Before Save macro is working


    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    Application.CutCopyMode = False


    End Sub

    Hi All

    I'm trying to run a macro in an external file and i get the

    Compile Error: Invalid outside procedure

    error message.

    I have referenced the external file thru Tools>References

    It highlights the first command in the external macro - so it finds the external macro OK but it don't want to run any commands in it.

    The first command was making a variable equal a text string - I commented that out and then it highlighted the next command
    which set a workbook variable to a file.

    I'm only doing this to get around the Before Save problem in this thread

    Any ideas?

    I can do what I want if I duplicate the before save macro code in the macro that is opening and saving the TotalCostsData file - then it works.

    But that is inefficient as I will have to duplicate it a number of times and this would make maintenance painful.

    Just been stepping thru (F8) the code.

    It activates the Before save macro but it doesn't perform the tasks.

    It went thru all the commands in the code - but it didn't DO anything eg when the AllAcountsSummary file was opened (supposedly) it did not appear on the screen (I turned on the screen updating so I could follow it).

    I put a msgbox where the code is doing the Autofilter stuff IN the AllAccountsSummary but the message box appears BEFORE the file is opened. Click OK and then the AllAccountSummary file is opened - weird!!!

    Also you don't seem to be able to Step (F8) thru the Before save macro itself - nothing happens.


    This code is copying a large sheet into a separate workbook and deleting all the zeros and blanks from the list.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    'this macro creates a summary of the All Accoutns sheet in the AllCostsData
    'to be used to provide data to the input files

    With Application
    .ScreenUpdating = False
    '.Calculation = xlCalculationManual

    'return focus to the AllCostsData file
    'ensure the correct sheet is active
    Sheets("All Accounts").Activate

    'copy the whole sheet

    Workbooks.Open Filename:= _
    "C:\Admin Managers\Copy of Budget2004\AllCostsSummary.xls"
    'gotp the destination file an paste the data
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    'delete unnecessary columns

    'use autofilter to select all the zero ot blank cells in the total column
    Range(Range("A6"), Range("q65536").End(xlUp)).Select
    Selection.AutoFilter Field:=17, Criteria1:="=0", Operator:=xlOr, _
    'clear the selected zero and blank rows

    'sort the data to put blanks lines at the end
    Selection.Sort Key1:=Range("q6"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    'save the workbook and close it

    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic

    End With


    End Sub

    Here's the code

    Dim Wb As Workbook
    Application.ScreenUpdating = False

    Set Wb = Workbooks.Open(Filename:= _
    "C:\Admin Managers\Copy of Budget2004\TotalCostsData.xls", UpdateLinks:=3)
    Application.EnableEvents = True

    I would have thought that the Before save macro would have been fired but it didn't.


    Hi everyone

    I've got a

    Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    macro in a workbook. When I Manually save the file it works no worries.


    When I open the file with a macro in another workbook and then save it with the ActiveWorkbook.Save command the before save macro doesn't fire.

    Any ideas?

    Surely it doesn't only work when you save it manually. (Stop calling me surely!)

    thanks in advance

    Don't be discouraged if it "seems" to be taking a long time - if you use VBA frequently and do a diverse range of tasks you will learn heaps within few months and then they'll be no stopping you.

    "Never, Never, Never give up." Winston Churchill
    Applies to life AND computers