Updating Excel Sheet

[AAnna16]

I have a workbook with about 12 sheets. All sheets update automatically. I want to have one sheet out of the 12 update manually by pressing a button. How can i do this?

Thanks in advance for any help. It is greatly appreciated

 

[AAnna16]

if anyone please has any ideas on how to even go about this please let me know.

thank you

 

[dannac]

If all 12 update automatically .... why the need to do one manually ?

.....where would the button be located ?

 

[AAnna16]

i have 11 sheets with data . the 12th sheet is compiled data for the month. i want this sheet to calculate only once a month or by a button i would have on the excel sheet.

 

[Sauron]

Hi Anna - just right click the toolbar and choose Customize. From there click the Commands tab and then click Macros in the Categories box. Choose Custom Button in the Commands box and drag it up to your toolbar. Close that screen then click the custom button to assign a macro to it. Let me know if you're on excel 2007+ as it's different.

 

[AAnna16]

Thanks very much for the button! i also am not sure how to code the VBA for having one sheet not updating. Do you know any ways to get around that?

Thank you again

 

[Sauron]

can you post some of the code in your macro? You may have to wrap in all in a loop that only loops through the sheets you want. Or another idea is to make the sheet you don't want calculated protected. That should prevent the macro from acting on that particular sheet.

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count 'put -1 here to ignore the last sheet

' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub

 

[AAnna16]

Thank you very much!
i don't have a code in place yet as to have it update/not update.

I am thinking the protected sheet idea might be a bit simpler. Only i tried protecting it and if i change data on other sheets it still updates on the final protected sheet

 

[Sauron]

oh okay you want to disable the formulas from calculating on the last sheet? Protection will disable VBA code from executing on the protected sheet.

To do this: click the last sheet then go to Tools | Options then click the Calculation tab. For calculation choose Manual - uncheck the Recalculate before save box.

Click OK

Now if you want it to calculate you don't even need a button - just press the F9 key.

 

[AAnna16]

This will stop all sheets from calculating no? all i want is the last sheet to not calculate