VBA Copy Range repeated

Question

I am trying to select a 14 rows in VBA, where I know the number of meter( starting cell). This rows are repeated in the table. I don't get what i wan with this code

Sub test()
Dim LastRow As Long


 Set sh = Sheets("Sheet1")

    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    Worksheets("Sheet1").Activate

  For x = 0 To LastRow

    If Worksheets("Sheet1").Cells(x, 2) = "58117552" Then                       
         Range(Cells(x, 2), Cells(X, 2)+14).copy
    End If
  Next 
End sub

Thanks for help.

I want to select all rows after searching meter Index ( for example: I search 58117552 in second column after select the 15th rows and copy them) and make same step for all table because i have other data for the same meter. — Abbas Abbas, Oct 22 '18 at 19:28

Wizhi · Accepted Answer · 2018-10-22T19:52:23.813

Since you always want to copy the 15 rows after each other, and in your example the number "Numéro de Compteuer" always appear at 2, 16, 30 (each 15 step) you could skip the search function and just copy every 15th row and what is below.

You need to define where you want to paste your copied range... I just took Sheet2.

Something to start with:

Sub test()
Dim LastRow As Long
Dim sh As Worksheet
Dim sh2 As Worksheet
Dim X As Long

Set sh = Sheets("Sheet1") 'Define sheet to copy from
Set sh2 = Sheets("Sheet2") 'Define sheet to copy to

LastRow = sh.Cells(Rows.Count, 2).End(xlUp).Row 'Find last row to copy from

For X = 2 To LastRow Step 15 'Jump 15 step each time (since you are interested in every 15 row)
    If Worksheets("Sheet1").Cells(X, 2) <> "" Then 'If cell is empty then
         Range(sh.Cells(X, 2), sh.Cells(X + 14, 2)).Copy 'Copy the range, where 15 rows downward will be copied each time
         sh2.Range(sh2.Cells(X, 5), sh2.Cells(X + 14, 5)).PasteSpecial xlPasteValues 'Paste somewhere
    End If
  Next
Application.CutCopyMode = False
End Sub

You have a ton of unqualified ranges. I'd update to fix those. — BruceWayne, Oct 22 '18 at 19:43
Could you please point out what you thinking about, so I can learn :)!! I appreciate your time and effort. — Wizhi, Oct 22 '18 at 19:45
See [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). See how you did `sh2.Range()`? That's a qualified range. You also should tell VBA what worksheet `Cells()` should be on. So, `sh2.Range(sh2.Cells(...),sh2.Cells(...))` for example — BruceWayne, Oct 22 '18 at 19:47

VBA Copy Range repeated

1 Answers1