-2

I am not used to writing code. I normally generate my code via macro and I am facing this issue. Can someone please help me?

     Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Rules").Select
Range("A2").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$307").RemoveDuplicates Columns:=1, Header:=xlYes
Sheets("Input").Select
ActiveWindow.LargeScroll Down:=-14
Range("A1").Select
Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Rules" _
    ).Range("A1:A2"), Unique:=False
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Result").Select
Range("A2").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(3, 0).Range("A1").Select
Sheets("Rules").Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("Input").Select
Range("A1").Select
Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Rules" _
    ).Range("A1:A2"), Unique:=False
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Result").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(3, 0).Range("A1").Select
Sheets("Rules").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("Input").Select
Range("A1").Select
Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Rules" _
    ).Range("A1:A2"), Unique:=False
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Result").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(3, 0).Range("A1").Select
Sheets("Rules").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("Input").Select
Range("A1").Select
Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Rules" _
    ).Range("A1:A2"), Unique:=False
ActiveCell.Offset(3, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Result").Select
ActiveSheet.Paste
End Sub

I want to repeat these steps 50 times, but I am getting an error message "Procedure too large" when I try to copy/paste it 50 times. Can you please show me how to do this in smaller steps?

Range("A1").Select
Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Rules" _
    ).Range("A1:A2"), Unique:=False
ActiveCell.Offset(2, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Result").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(3, 0).Range("A1").Select
Sheets("Rules").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("Input").Select
Phrancis
  • 2,222
  • 2
  • 27
  • 40
Nikhil Joseph
  • 41
  • 3
  • 8
  • 9
    Research how to shrink your code by condensing all of those select statements. Next, research using loops in VBA. Google both of those subjects and you'll be well on your way...good luck! – sous2817 Aug 23 '14 at 19:50
  • 1
    Before you do anything else, [get rid of those Select statements](http://stackoverflow.com/q/10714251/445425) – chris neilsen Aug 23 '14 at 20:49
  • Welcome to Stack Overflow. For some starting points for your "research" you can use those I have collected in different Excel-related answer at http://stackoverflow.com/a/24401823/2626313 – xmojmr Aug 25 '14 at 18:43
  • 1
    Thanks a lot guys. did a little bit of research about looping and re-wrote my code using do loop until isempty code . this was really helpful. much obliged.... :) – Nikhil Joseph Aug 26 '14 at 15:54

1 Answers1

2

Save only the steps that you need to repeat in a macro, say Macro2.

Then create a new Sub like this:

Sub RepeatMacro2

' Beginning steps (will not be repeated)

For i = 1 to 50
    Macro2
Next i

' Final steps (will not be repeated)

End Sub

You can copy/paste the first steps (not to be repeated) before the For and the last steps (also not to be repeated) after the Next.

Call RepeatMacro2 using the Macros dialog.

Emilio Silva
  • 1,942
  • 14
  • 17
  • 1
    OP will probably need to learn about variables and passing arguments to subroutines as well. – RubberDuck Aug 23 '14 at 22:01
  • 1
    Thank you Emilio. I used the step you showed above and got the desired results but the number 50 was not always constant. So i did a little bit of researching on loops and used the do loop until is empty code. Hope you are happy with the progress i have made in the past few days. Thanks for all the help and support.... :) – Nikhil Joseph Aug 26 '14 at 15:56