Macro do works in excel...

[Guest]

Hi again,

How to write in this code by
1) making the workbook to be set in Manual Calc. mode, before population of
template sheets,
2)after population of the sheets, return back to Automatic calc mode.

here's the wonderful <guided> typical code from Sir Toppers
----------
Sub SetUp()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim irow As Long, orow As Long
Dim Lastrow As Long

Dim wsname As String

Set ws1 = Worksheets("LIST")
Set ws2 = Worksheets("TEMPLATE")
Set ws3 = Worksheets("CAT")
Set ws4 = Worksheets("DATA")

With ws1

Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

For irow = 2 To Lastrow
wsname = .Cells(irow, 2)
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = wsname
ActiveSheet.Range("H1") = wsname
.Cells(irow, 33).Formula = "='" & wsname & "'!h54"
.Cells(irow, 34).Formula = "='" & wsname & "'!h53"
.Cells(irow, 35).Formula = "='" & wsname & "'!h52"
.Cells(irow, 36).Formula = "='" & wsname & "'!h51"
.Cells(irow, 37).Formula = "='" & wsname & "'!h50"
.Cells(irow, 38).Formula = "='" & wsname & "'!J9"
.Cells(irow, 39).Formula = "='" & wsname & "'!C53"
.Cells(irow, 40).Formula = "='" & wsname & "'!C52"
.Cells(irow, 41).Formula = "='" & wsname & "'!C51"


Next irow

End With


End Sub

 

[krcowen]

driller
Put

Application.Calculation = xlManual

at the beginning. And

Application.Calculation = xlAutomatic

at the end.

Good luck.

Ken
Norfolk, Va

 

[Guest]

thanks ken,

learning by experience is only 2 cents worth in this forum...as someone
quoted here before...Yet, I still dont know how to study excel - outside of
this forum !

regards,
driller
--
*****
birds of the same feather flock together..

driller
Put

Application.Calculation = xlManual

at the beginning. And

Application.Calculation = xlAutomatic

at the end.

Good luck.

Ken
Norfolk, Va

 

[Don Guillett]

Sometimes it is useful to help yourself. Had you bothered to goto the vba
help index and type in calculation you would have had as the first hit:
Calculation Property
See AlsoApplies ToExampleSpecifics
Calculation property as it applies to the Application object.

Returns or sets the calculation mode. Read/write XlCalculation.

XlCalculation can be one of these XlCalculation constants.
xlCalculationAutomatic
xlCalculationManual
xlCalculationSemiautomatic

expression.Calculation

expression Required. An expression that returns one of the above objects.

Calculation property as it applies to the PivotField object.

Returns or sets the type of calculation performed by the specified field.
This property is valid only for data fields. Read/write
XlPivotFieldCalculation.

XlPivotFieldCalculation can be one of these XlPivotFieldCalculation
constants.
xlDifferenceFrom
xlIndex
xlNoAdditionalCalculation
xlPercentDifferenceFrom
xlPercentOf
xlPercentOfColumn
xlPercentOfRow
xlPercentOfTotal
xlRunningTotal

expression.Calculation

expression Required. An expression that returns one of the above objects.

Remarks
For OLAP data sources, this property can only return or be set to xlNormal.

Example
This example causes Microsoft Excel to calculate workbooks before they are
saved to disk.

Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = True
This example sets the data field in the PivotTable report on Sheet1 to
calculate the difference from the base field, sets the base field to the
field named "ORDER_DATE," and then sets the base item to the item named
"5/16/89."

With Worksheets("Sheet1").Range("A3").PivotField
.Calculation = xlDifferenceFrom
.BaseField = "ORDER_DATE"
.BaseItem = "5/16/89"
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

thanks ken,

learning by experience is only 2 cents worth in this forum...as someone
quoted here before...Yet, I still dont know how to study excel - outside
of
this forum !

regards,
driller

 

[Guest]

Thanks Don,

After some unfinished threads we had been with... I hope u noticed that i
mostly focus on a very simple yet intensively specific matter...i am not
wishing to be a developer of whatever macros or programs since a lot over
here deserve this *space*...I only ask for what my work ask -to date- while
knowing or learning the extents like the word *impossible* in a formula/macro
...... to harmonize with the daily excel user!!.

regards,
driller

 

[Guest]

Hi MVP Sir Don,

Maybe you can also help me with my last macro problem in a previous post i
made today with titled subject "Subject: help to count by macro or whatever
simple and easy.... " just for 2 cents!

regards and thanks for your kind help.
driller

 

[Don Guillett]

I don't find any such post.

 

[Guest]

Oops, Sorry i forget to say i posted in excel Prog./GenQ group....
Anyway, i will re-post it today...

regards,
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull