Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient way?
Hi everyone. Was hoping someone had some ideas on how to speed up the
following code. The base code was started using the macro-recorder. I selected several sheets using the ctl key, then copied row 2 with formulas in it (each sheet has a row of formulas in the same row), then pasted it to a range. It works....just slowly. I'm running a 2.4G processor with 2.0G of memory...and it still takes forever! Granted, each sheet is being populated with 13,000 cells, but I still didn't think it would take this long. Have a better method?? Thanks! Sub Forecast() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv Cab", "Ntwk Kit", _ "TDAX", "EMS", "SCOUT", "Dir Coup")) shtarray.Select Sheets("LMU").Activate Range("A2:EC2").Copy Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row) frng.PasteSpecial Paste:=xlPasteFormulas Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient way?
Thanks for the response Don. That does exactly what I wanted done. But
unfortunately, it's no faster than a similar For Next loop I have....as a matter of fact, the timing of the 2 procedures are almost identical! Oh well, Thanks again! "Don Guillett" wrote in message ... I don't know if this is what you want but this will copy row 2 formulas in each sheet down to the last cell in col B of each worksheet. Sub fc() Set shtarray = Sheets(Array("LMU", "Kit", "SMLC")) For Each sh In shtarray With sh x = .Cells(Rows.Count, "B").End(xlUp).Row .Rows(2).Copy .Rows("2:" & x) End With Next End Sub -- Don Guillett SalesAid Software "Steph" wrote in message ... Hi everyone. Was hoping someone had some ideas on how to speed up the following code. The base code was started using the macro-recorder. I selected several sheets using the ctl key, then copied row 2 with formulas in it (each sheet has a row of formulas in the same row), then pasted it to a range. It works....just slowly. I'm running a 2.4G processor with 2.0G of memory...and it still takes forever! Granted, each sheet is being populated with 13,000 cells, but I still didn't think it would take this long. Have a better method?? Thanks! Sub Forecast() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv Cab", "Ntwk Kit", _ "TDAX", "EMS", "SCOUT", "Dir Coup")) shtarray.Select Sheets("LMU").Activate Range("A2:EC2").Copy Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row) frng.PasteSpecial Paste:=xlPasteFormulas Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient way?
try using the autofill idea
Range("f2:i2").AutoFill Destination:=Range("f2:i6") -- Don Guillett SalesAid Software "Steph" wrote in message ... Thanks for the response Don. That does exactly what I wanted done. But unfortunately, it's no faster than a similar For Next loop I have....as a matter of fact, the timing of the 2 procedures are almost identical! Oh well, Thanks again! "Don Guillett" wrote in message ... I don't know if this is what you want but this will copy row 2 formulas in each sheet down to the last cell in col B of each worksheet. Sub fc() Set shtarray = Sheets(Array("LMU", "Kit", "SMLC")) For Each sh In shtarray With sh x = .Cells(Rows.Count, "B").End(xlUp).Row .Rows(2).Copy .Rows("2:" & x) End With Next End Sub -- Don Guillett SalesAid Software "Steph" wrote in message ... Hi everyone. Was hoping someone had some ideas on how to speed up the following code. The base code was started using the macro-recorder. I selected several sheets using the ctl key, then copied row 2 with formulas in it (each sheet has a row of formulas in the same row), then pasted it to a range. It works....just slowly. I'm running a 2.4G processor with 2.0G of memory...and it still takes forever! Granted, each sheet is being populated with 13,000 cells, but I still didn't think it would take this long. Have a better method?? Thanks! Sub Forecast() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv Cab", "Ntwk Kit", _ "TDAX", "EMS", "SCOUT", "Dir Coup")) shtarray.Select Sheets("LMU").Activate Range("A2:EC2").Copy Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row) frng.PasteSpecial Paste:=xlPasteFormulas Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient way?
Hi Don,
Thanks for the new suggestion. I edited your piece of code to fit my range, and I got an Autofill method of range class failed error. Is that because my formula is in row 2, and I don't want the autofill to start until row 5? My code is : Set shtarray = Sheets(Array("Sheet1", "Sheet2", "Sheet3")) For Each sh In shtarray Range("A2:EC2").AutoFill Destination:=Range("A5:EC" & Data.Range("B65536").End(xlUp).Row) With sh.Range("A5:EC" & Data.Range("B65536").End(xlUp).Row) .Value = .Value End With Next sh Thanks again Don!! "Don Guillett" wrote in message ... try using the autofill idea Range("f2:i2").AutoFill Destination:=Range("f2:i6") -- Don Guillett SalesAid Software "Steph" wrote in message ... Thanks for the response Don. That does exactly what I wanted done. But unfortunately, it's no faster than a similar For Next loop I have....as a matter of fact, the timing of the 2 procedures are almost identical! Oh well, Thanks again! "Don Guillett" wrote in message ... I don't know if this is what you want but this will copy row 2 formulas in each sheet down to the last cell in col B of each worksheet. Sub fc() Set shtarray = Sheets(Array("LMU", "Kit", "SMLC")) For Each sh In shtarray With sh x = .Cells(Rows.Count, "B").End(xlUp).Row .Rows(2).Copy .Rows("2:" & x) End With Next End Sub -- Don Guillett SalesAid Software "Steph" wrote in message ... Hi everyone. Was hoping someone had some ideas on how to speed up the following code. The base code was started using the macro-recorder. I selected several sheets using the ctl key, then copied row 2 with formulas in it (each sheet has a row of formulas in the same row), then pasted it to a range. It works....just slowly. I'm running a 2.4G processor with 2.0G of memory...and it still takes forever! Granted, each sheet is being populated with 13,000 cells, but I still didn't think it would take this long. Have a better method?? Thanks! Sub Forecast() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv Cab", "Ntwk Kit", _ "TDAX", "EMS", "SCOUT", "Dir Coup")) shtarray.Select Sheets("LMU").Activate Range("A2:EC2").Copy Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row) frng.PasteSpecial Paste:=xlPasteFormulas Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient way?
Yep. Never hurts to highlight the word and touch the F1 key. Amazing what
you get. Maybe you could clear/hide the interveing rows after the fill. AutoFill Method See Also Applies To Example Specifics Performs an autofill on the cells in the specified range. Variant. expression.AutoFill(Destination, Type) expression Required. An expression that returns one of the objects in the Applies To list. Destination Required Range object. The cells to be filled. The destination must include the source range. Type Optional XlAutoFillType. Specifies the fill type. XlAutoFillType can be one of these XlAutoFillType constants. xlFillDays xlFillFormats xlFillSeries xlFillWeekdays xlGrowthTrend xlFillCopy xlFillDefault default xlFillMonths xlFillValues xlFillYears xlLinearTrend If this argument is xlFillDefault or omitted, Microsoft Excel selects the most appropriate fill type, based on the source range. Example This example performs an autofill on cells A1:A20 on Sheet1, based on the source range A1:A2 on Sheet1. Before running this example, type 1 in cell A1 and type 2 in cell A2. Set sourceRange = Worksheets("Sheet1").Range("A1:A2") Set fillRange = Worksheets("Sheet1").Range("A1:A20") sourceRange.AutoFill Destination:=fillRange -- Don Guillett SalesAid Software "Steph" wrote in message ... Hi Don, Thanks for the new suggestion. I edited your piece of code to fit my range, and I got an Autofill method of range class failed error. Is that because my formula is in row 2, and I don't want the autofill to start until row 5? My code is : Set shtarray = Sheets(Array("Sheet1", "Sheet2", "Sheet3")) For Each sh In shtarray Range("A2:EC2").AutoFill Destination:=Range("A5:EC" & Data.Range("B65536").End(xlUp).Row) With sh.Range("A5:EC" & Data.Range("B65536").End(xlUp).Row) .Value = .Value End With Next sh Thanks again Don!! "Don Guillett" wrote in message ... try using the autofill idea Range("f2:i2").AutoFill Destination:=Range("f2:i6") -- Don Guillett SalesAid Software "Steph" wrote in message ... Thanks for the response Don. That does exactly what I wanted done. But unfortunately, it's no faster than a similar For Next loop I have....as a matter of fact, the timing of the 2 procedures are almost identical! Oh well, Thanks again! "Don Guillett" wrote in message ... I don't know if this is what you want but this will copy row 2 formulas in each sheet down to the last cell in col B of each worksheet. Sub fc() Set shtarray = Sheets(Array("LMU", "Kit", "SMLC")) For Each sh In shtarray With sh x = .Cells(Rows.Count, "B").End(xlUp).Row .Rows(2).Copy .Rows("2:" & x) End With Next End Sub -- Don Guillett SalesAid Software "Steph" wrote in message ... Hi everyone. Was hoping someone had some ideas on how to speed up the following code. The base code was started using the macro-recorder. I selected several sheets using the ctl key, then copied row 2 with formulas in it (each sheet has a row of formulas in the same row), then pasted it to a range. It works....just slowly. I'm running a 2.4G processor with 2.0G of memory...and it still takes forever! Granted, each sheet is being populated with 13,000 cells, but I still didn't think it would take this long. Have a better method?? Thanks! Sub Forecast() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv Cab", "Ntwk Kit", _ "TDAX", "EMS", "SCOUT", "Dir Coup")) shtarray.Select Sheets("LMU").Activate Range("A2:EC2").Copy Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row) frng.PasteSpecial Paste:=xlPasteFormulas Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
More efficient way?
Thanks for all the info Don. I'll try adding making the formula row
directly above the autofill range. The small change to my workbook structure will be well worth it if autofill speeds the code up! If not, oh well.....the 'ol college try! Thanks again for all your help!! "Don Guillett" wrote in message ... Yep. Never hurts to highlight the word and touch the F1 key. Amazing what you get. Maybe you could clear/hide the interveing rows after the fill. AutoFill Method See Also Applies To Example Specifics Performs an autofill on the cells in the specified range. Variant. expression.AutoFill(Destination, Type) expression Required. An expression that returns one of the objects in the Applies To list. Destination Required Range object. The cells to be filled. The destination must include the source range. Type Optional XlAutoFillType. Specifies the fill type. XlAutoFillType can be one of these XlAutoFillType constants. xlFillDays xlFillFormats xlFillSeries xlFillWeekdays xlGrowthTrend xlFillCopy xlFillDefault default xlFillMonths xlFillValues xlFillYears xlLinearTrend If this argument is xlFillDefault or omitted, Microsoft Excel selects the most appropriate fill type, based on the source range. Example This example performs an autofill on cells A1:A20 on Sheet1, based on the source range A1:A2 on Sheet1. Before running this example, type 1 in cell A1 and type 2 in cell A2. Set sourceRange = Worksheets("Sheet1").Range("A1:A2") Set fillRange = Worksheets("Sheet1").Range("A1:A20") sourceRange.AutoFill Destination:=fillRange -- Don Guillett SalesAid Software "Steph" wrote in message ... Hi Don, Thanks for the new suggestion. I edited your piece of code to fit my range, and I got an Autofill method of range class failed error. Is that because my formula is in row 2, and I don't want the autofill to start until row 5? My code is : Set shtarray = Sheets(Array("Sheet1", "Sheet2", "Sheet3")) For Each sh In shtarray Range("A2:EC2").AutoFill Destination:=Range("A5:EC" & Data.Range("B65536").End(xlUp).Row) With sh.Range("A5:EC" & Data.Range("B65536").End(xlUp).Row) .Value = .Value End With Next sh Thanks again Don!! "Don Guillett" wrote in message ... try using the autofill idea Range("f2:i2").AutoFill Destination:=Range("f2:i6") -- Don Guillett SalesAid Software "Steph" wrote in message ... Thanks for the response Don. That does exactly what I wanted done. But unfortunately, it's no faster than a similar For Next loop I have....as a matter of fact, the timing of the 2 procedures are almost identical! Oh well, Thanks again! "Don Guillett" wrote in message ... I don't know if this is what you want but this will copy row 2 formulas in each sheet down to the last cell in col B of each worksheet. Sub fc() Set shtarray = Sheets(Array("LMU", "Kit", "SMLC")) For Each sh In shtarray With sh x = .Cells(Rows.Count, "B").End(xlUp).Row .Rows(2).Copy .Rows("2:" & x) End With Next End Sub -- Don Guillett SalesAid Software "Steph" wrote in message ... Hi everyone. Was hoping someone had some ideas on how to speed up the following code. The base code was started using the macro-recorder. I selected several sheets using the ctl key, then copied row 2 with formulas in it (each sheet has a row of formulas in the same row), then pasted it to a range. It works....just slowly. I'm running a 2.4G processor with 2.0G of memory...and it still takes forever! Granted, each sheet is being populated with 13,000 cells, but I still didn't think it would take this long. Have a better method?? Thanks! Sub Forecast() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab", "Serv Cab", "Ntwk Kit", _ "TDAX", "EMS", "SCOUT", "Dir Coup")) shtarray.Select Sheets("LMU").Activate Range("A2:EC2").Copy Set frng = Range("A5:EC" & Data.Range("b65536").End(xlUp).Row) frng.PasteSpecial Paste:=xlPasteFormulas Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is more efficient | Excel Discussion (Misc queries) | |||
is there a more efficient formula than... | Excel Worksheet Functions | |||
Which is more efficient? | Excel Programming | |||
Efficient Looping | Excel Programming | |||
Is there more efficient formula? | Excel Programming |