Needs Help With Calling All Tabs

[ham123]

Greetings Experts,

I have a code which allows me to refresh calculations for tabs, however, it is not the most versatile as it is calling tabs separately. This would not be feasible if I had 200 over tabs.
I need help editing it such that it would refresh all tabs in the workbook.


Sub Button1_Click() Application.ScreenUpdating = False
Call CopyPasteValuesInTab("Allocation")
Call CopyPasteValuesInTab("By Ctry-EIN")
Call CopyPasteValuesInTab("By Ctry-EMSB")
Call CopyPasteValuesInTab("By Ctry-ETH")
Call CopyPasteValuesInTab("By Ctry-EPC")
Call CopyPasteValuesInTab("ESD Trf Qty")
'...
ActiveSheet.Calculate
Application.ScreenUpdating = True

MsgBox "Done!"
End Sub

Any help is much appreciated! :)

[JKwan]

This should do the trick


Sub Button1_Click()
Dim WS As Worksheet

Application.ScreenUpdating = False
For Each WS In ThisWorkbook.Worksheets
Call CopyPasteValuesInTab(WS.Name)
Next WS

ActiveSheet.Calculate
Application.ScreenUpdating = True

MsgBox "Done!"
End Sub

[ham123]

Hi, thank you for your reply! It works for me :)

[ham123]

For your reference, this is the full code

Private Sub CommandButton1_Click()

Call Button1_Click


End Sub


Sub Button1_Click()
Dim WS As Worksheet

Application.ScreenUpdating = False
For Each WS In ThisWorkbook.Worksheets
Call CopyPasteValuesInTab(WS.Name)
Next WS

ActiveSheet.Calculate
Application.ScreenUpdating = True

MsgBox "Done!"


End Sub


Sub CopyPasteValuesInTab(ByVal SheetName As String)
On Error GoTo EH
Dim WS As Worksheet
Set WS = Sheets(SheetName)
WS.Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Exit Sub
EH:
Debug.Print Err.Number & ": " & Err.Description
Application.CutCopyMode = False
End Sub

[大灰狼1976]

All of this can be simplified into the following codes:

Private Sub CommandButton1_Click()
Dim sh As Worksheet
For Each sh In Worksheets
sh.UsedRange = sh.UsedRange
Next sh
MsgBox "Done!"
End Sub

[snb]

Why not:


Sub M_snb()
Application.calculate
End Sub

or

Sub M_snb()
sheet.select
selection.calculate
End Sub