-2

I'd like to make my code shorter due to getting error "Procedure too Large". How can I write code so it would take range A2 to A10 from one worksheet, open my other worksheet and paste it into B214 to B222 in that exact order. Right now that code works well. But when I make 200 of those it will give me error.

Private Sub CommandButton1_Click()
Dim EAN As String
Worksheets("Button Excel").Select
EAN = Range("A2")
Worksheets("Magic").Select
Worksheets("Magic").Range("B214").Select
ActiveCell.Value = EAN
EAN = Range("A3")
Worksheets("Magic").Select
Worksheets("Magic").Range("B215").Select
ActiveCell.Value = EAN
EAN = Range("A4")
Worksheets("Magic").Select
Worksheets("Magic").Range("B216").Select
ActiveCell.Value = EAN
EAN = Range("A5")
Worksheets("Magic").Select
Worksheets("Magic").Range("B217").Select
ActiveCell.Value = EAN
EAN = Range("A6")
Worksheets("Magic").Select
Worksheets("Magic").Range("B218").Select
ActiveCell.Value = EAN
EAN = Range("A7")
Worksheets("Magic").Select
Worksheets("Magic").Range("B219").Select
ActiveCell.Value = EAN
EAN = Range("A8")
Worksheets("Magic").Select
Worksheets("Magic").Range("B220").Select
ActiveCell.Value = EAN
EAN = Range("A9")
Worksheets("Magic").Select
Worksheets("Magic").Range("B221").Select
ActiveCell.Value = EAN
EAN = Range("A10")
Worksheets("Magic").Select
Worksheets("Magic").Range("B222").Select
ActiveCell.Value = EAN
End Sub
vacip
  • 5,246
  • 2
  • 26
  • 54
ohh hh
  • 3
  • 1
  • 2
    See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). –  Mar 28 '17 at 14:11
  • Does VBA really have a max length of procedures? I have never hit that point before. – Kyle Mar 28 '17 at 15:10

1 Answers1

3

If all you need is to copy the values from Range("A2:A10") in "Button Excel" sheet to Range("B214:B222") in "Magic" sheet, use the 1 line of code below:

Worksheets("Magic").Range("B214:B222").Value = Worksheets("Button Excel").Range("A2:A10").Value
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • ... or for '*200 of those*' `Worksheets("Magic").Range("B214:B413") = Worksheets("Button Excel").Range("A2:A201").Value` –  Mar 28 '17 at 14:13
  • The OP stated '*Right now that code works well. But when I make 200 of those it will give me error.*' in his/her narrative. –  Mar 28 '17 at 14:15
  • @Jeeped OH, since it was hard for me to "Guess" what he meant, I wrote in my answer how to shorten it for the code he posted. Once he explains better, or provide all the code he needs, I'll add an update – Shai Rado Mar 28 '17 at 14:16
  • Yeah, I just added my addendum in comments to demonstrate that the source and the target must be the same size (and shape unless transpose is added). –  Mar 28 '17 at 14:18