Faster copy-paste macro - easiest way?
 

Hi

I need help to make my macro work faster. I assume that some arra
coding can do the trick. Because the problem is that the macro read an
write all the time. I need the macro to read and when finished write.

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.

For example; if x in column "Monday" paste that row on sheet "Monday"
If x in both column "Monday" and "Tuesday" paste that row on sheet
"Monday" and "Tuesday".

The "base" sheet look like this (ignore the.....):

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

--
Message posted from http://www.ExcelForum.com

Faster copy-paste macro - easiest way?
 

Apply an autofilter to your data

then loop through c and for each column, filter on X, then copy the range to
the appropriate sheet. Only the visible rows (rows with X) will be copied.
Remove the filter condition at the end of the loop before you move on to the
next column.

You can get the basic code you need by using the macro recorder.

--
Regards,
Tom Ogilvy

"freseh " wrote in message
...
Hi

I need help to make my macro work faster. I assume that some array
coding can do the trick. Because the problem is that the macro read and
write all the time. I need the macro to read and when finished write.

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.

For example; if x in column "Monday" paste that row on sheet "Monday".
If x in both column "Monday" and "Tuesday" paste that row on sheets
"Monday" and "Tuesday".

The "base" sheet look like this (ignore the.....):

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.


---
Message posted from http://www.ExcelForum.com/