Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Looking for faster method for Copy & Paste | Excel Programming | |||
copy & paste macro | Excel Programming | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming | |||
What is the easiest way to setup a 'global' macro which is always available to Excel on a PC? | Excel Programming |