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

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/