-1

Looking for a VBA routine that will allow the same copy and paste procedure to happen repeatedly while moving through a long worksheet of data. I'm interested in copying a small area of cells that repeat through out a worksheet and are the same distance away from the next selection area I need.

Sheets("Excel_Essex_Helos_Dump_80520_12").Select
Range("B6").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B4").Select
ActiveSheet.Paste
Sheets("Excel_Essex_Helos_Dump_80520_12").Select
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B5").Select
ActiveSheet.Paste
Sheets("Excel_Essex_Helos_Dump_80520_12").Select
Range("F6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B6").Select
ActiveSheet.Paste
Sheets("Excel_Essex_Helos_Dump_80520_12").Select
ActiveWindow.SmallScroll Down:=24
Range("A47:A73").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B7").Select
ActiveSheet.Paste

So basically on the next go around I want to select "B79" and then "D79" and "F79" etc. Each next cell will be 73 cells away from the previous one. Is there a routine I can create that adds 73 to the range and repeats until finished?

Justin
  • 357
  • 2
  • 7
  • 18
  • 1
    `For i = 1 to 50,000 Step 73` Read about For Loops. – Scott Holtzman Dec 29 '16 at 14:30
  • Although it isn't pertaining to your immediate question, take a look at [this SO question and answers](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) about how to avoid select statements. It will help your code immensely. – PartyHatPanda Dec 29 '16 at 15:29

1 Answers1

0

This is not the answer, but you can easily Copy >> Paste without using Select and ActiveSheet all the time.

If you want to copy (and include formatting and/or formulas) use :

With Sheets("Excel_Essex_Helos_Dump_80520_12")
    .Range("B6").Copy Destination:=Sheets("Sheet1").Range("B4")
End With

If you want to copy only the values you can use :

With Sheets("Excel_Essex_Helos_Dump_80520_12")
    Sheets("Sheet1").Range("B4").Value = .Range("B6").Value
End With

Now, you can incorporate the Row Number and Column Number for the source and destination worksheets.

Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • Thanks Shai, this is definitely helpful. Any idea on how to get this routine to repeat throughout a really long worksheet. I think the cells go all the way down to 50,000.... – Justin Dec 29 '16 at 14:14