Urgent Help - Improve macro speed

**Mysore** · 02-05-2012, 10:01 PM

Hi Guys,

The macro below works perfectly fine. It creates 250 rows in Sheet 1 and copies formula . Then it does the similar

thing in Sheet 2. However this macro runs slowly. Can any one speed it up?

Thanks

Sub InsertRowS1()
'
ActiveSheet.Unprotect Password:="team"

' Stop screen refresh
' Application.ScreenUpdating = False

' Insert Blank Row
Worksheets("Data Input -Unit Leaders").Activate
Application.Goto Reference:="R1000000C1"
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Select
Rows(ActiveCell.Row).Select

' Loop 100 times
i = 1
Do
Rows(ActiveCell.Row).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Copy Formulas, & paste in blank row
ActiveCell.Offset(3, 0).Select
Rows(ActiveCell.Row).Select
Selection.Copy
ActiveCell.Offset(-3, 0).Select
Rows(ActiveCell.Row).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop Until i = 250
ActiveSheet.Protect Password:="team"

' Insert Blank Row
Worksheets("Ranking - Unit Leaders").Activate
ActiveSheet.Unprotect Password:="team"
Application.Goto Reference:="R1000000C1"
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Select

' Loop 100 times
i = 1
Do
Rows(ActiveCell.Row).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Copy Formulas, & paste in blank row
ActiveCell.Offset(-1, 0).Select
Rows(ActiveCell.Row).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Rows(ActiveCell.Row).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop Until i = 250
ActiveSheet.Protect Password:="team"

' Start Screen refresh
Application.ScreenUpdating = True

End Sub

**protonLeah** · 02-05-2012, 10:49 PM

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

[code]
your code here ...
and here ...
and here
[/code]
You can also type the code tags in manually if you prefer. For more information about these and other tags, click here.

**ConneXionLost** · 02-05-2012, 11:38 PM

Mysore,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Also,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Urgent Help - Improve macro speed

LinkBack

Thread Tools

Display

Urgent Help - Improve macro speed

Re: Urgent Help - Improve macro speed

Re: Urgent Help - Improve macro speed

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions