| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro do works in excel...
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 --------- thanks for help, driller -- ***** birds of the same feather flock together.. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro do works in excel...
driller
Put Application.Calculation = xlManual at the beginning. And Application.Calculation = xlAutomatic at the end. Good luck. Ken Norfolk, Va On Jul 11, 2:00 pm, driller wrote: 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 --------- thanks for help, driller -- ***** birds of the same feather flock together.. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro do works in excel...
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.. " wrote: driller Put Application.Calculation = xlManual at the beginning. And Application.Calculation = xlAutomatic at the end. Good luck. Ken Norfolk, Va On Jul 11, 2:00 pm, driller wrote: 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 --------- thanks for help, driller -- ***** birds of the same feather flock together.. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro do works in excel...
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 "driller" wrote in message ... 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.. " wrote: driller Put Application.Calculation = xlManual at the beginning. And Application.Calculation = xlAutomatic at the end. Good luck. Ken Norfolk, Va On Jul 11, 2:00 pm, driller wrote: 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 --------- thanks for help, driller -- ***** birds of the same feather flock together.. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro do works in excel...
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 -- ***** birds of the same feather flock together.. "Don Guillett" wrote: 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 "driller" wrote in message ... 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.. " wrote: driller Put Application.Calculation = xlManual at the beginning. And Application.Calculation = xlAutomatic at the end. Good luck. Ken Norfolk, Va On Jul 11, 2:00 pm, driller wrote: 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 --------- thanks for help, driller -- ***** birds of the same feather flock together.. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro do works in excel...
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 -- ***** birds of the same feather flock together.. "driller" wrote: 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 -- ***** birds of the same feather flock together.. "Don Guillett" wrote: 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 "driller" wrote in message ... 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.. " wrote: driller Put Application.Calculation = xlManual at the beginning. And Application.Calculation = xlAutomatic at the end. Good luck. Ken Norfolk, Va On Jul 11, 2:00 pm, driller wrote: 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 --------- thanks for help, driller -- ***** birds of the same feather flock together.. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro do works in excel...
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 "Don Guillett" wrote: I don't find any such post. -- Don Guillett Microsoft MVP Excel SalesAid Software "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. "driller" wrote: 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 -- ***** birds of the same feather flock together.. "Don Guillett" wrote: 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 "driller" wrote in message ... 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.. " wrote: driller Put Application.Calculation = xlManual at the beginning. And Application.Calculation = xlAutomatic at the end. Good luck. Ken Norfolk, Va On Jul 11, 2:00 pm, driller wrote: 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 --------- thanks for help, driller -- ***** birds of the same feather flock together.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Email Macro only sometime works? | Excel Discussion (Misc queries) | |||
| Excel macro works only for administrator | Excel Discussion (Misc queries) | |||
| Tab-naming macro only works once :-( | Excel Worksheet Functions | |||
| macro works for some users and doesn't for others | Excel Discussion (Misc queries) | |||
| How do I convert works file to excel without works software? | Excel Discussion (Misc queries) | |||
Linear Mode
