Updating Excel Sheet

AAnna16

New Member
Joined
Sep 8, 2009
Messages
8
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 :):eek:
 

AAnna16

New Member
Joined
Sep 8, 2009
Messages
8
if anyone please has any ideas on how to even go about this please let me know.

thank you
 

dannac

Active Member
Joined
Nov 17, 2004
Messages
472
If all 12 update automatically .... why the need to do one manually ?

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

AAnna16

New Member
Joined
Sep 8, 2009
Messages
8
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

New Member
Joined
Sep 8, 2009
Messages
41
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

New Member
Joined
Sep 8, 2009
Messages
8
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

New Member
Joined
Sep 8, 2009
Messages
41
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

New Member
Joined
Sep 8, 2009
Messages
8
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

New Member
Joined
Sep 8, 2009
Messages
41
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

New Member
Joined
Sep 8, 2009
Messages
8
This will stop all sheets from calculating no? all i want is the last sheet to not calculate
 

Forum statistics

Threads
1,082,020
Messages
5,362,713
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top