Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - speed up macro, working with array?
Hi
I want to make my macro works faster. I've tried to convert this cod into an "array" code, but I get into some problems.....so I need som help. The macro works like this. When you push an update button the macro compiles a list on differen sheets depending if the row is marked with a "x" or not. If "x" i copies a range, column 2 to 4, on that row and paste it on that shee that the x refers to. The "base" sheet look like this: Nr Activity instructions time Monday Tuesday etc.... 1 Test1 Do this1 1 hour x 2 Test2 Do this2 5 hours x x r is rows and c is columns. The slow code is as follows: Sub List() Dim r As Integer Dim c As Integer Application.ScreenUpdating = False For c = 5 To 12 For r = 6 To 300 Sheets("Base").Select If Cells(r, c) = "x" Then Range(Cells(r, 2), Cells(r, 4)).Copy Sheets(c - 4).Select If Cells(4, 3) = "" Then Cells(4, 3).Select Selection.PasteSpecial Paste:=xlValues Else ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues End If End If Next r Next c Application.ScreenUpdating = True End Sub Many thanks in advance. All the best Fre -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - speed up macro, working with array?
Hi,
Get rid of the : this.select - selections.do_that Use Worksheet("Base").range(your range).copy (destination), where destination= Worksheet("bbbb").range(your other range) or : With Worksheet("Base") .range(your range).copy Worksheet("bbbb").range(your other range) .range(another range).copy Worksheet("bbbb").range(your other range) end with Regards, Jean-Yves "freseh " wrote in message ... Hi I want to make my macro works faster. I've tried to convert this code into an "array" code, but I get into some problems.....so I need some help. The macro works like this. When you push an update button the macro compiles a list on different sheets depending if the row is marked with a "x" or not. If "x" it copies a range, column 2 to 4, on that row and paste it on that sheet that the x refers to. The "base" sheet look like this: Nr Activity instructions time Monday Tuesday etc.... 1 Test1 Do this1 1 hour x 2 Test2 Do this2 5 hours x x r is rows and c is columns. The slow code is as follows: Sub List() Dim r As Integer Dim c As Integer Application.ScreenUpdating = False For c = 5 To 12 For r = 6 To 300 Sheets("Base").Select If Cells(r, c) = "x" Then Range(Cells(r, 2), Cells(r, 4)).Copy Sheets(c - 4).Select If Cells(4, 3) = "" Then Cells(4, 3).Select Selection.PasteSpecial Paste:=xlValues Else ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlValues End If End If Next r Next c Application.ScreenUpdating = True End Sub Many thanks in advance. All the best Fred --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed of array formula | Excel Worksheet Functions | |||
Speed up macro | Excel Discussion (Misc queries) | |||
Worksheet speed with array formula | Excel Discussion (Misc queries) | |||
Help, need to speed up this macro | Excel Discussion (Misc queries) | |||
Speed-up macro | Excel Programming |