Run a macro to VBA automated function

[Unexpc]

Hi guys
I have this code from a professional member of mrexcel

Sub MyCopyRange()

    Dim nr As Long
    Dim i As Long
        Application.ScreenUpdating = False

'   Set initial value of next row
    nr = 34
    
'   Copy range
    For i = 2 To 30840 Step 1
'   Copy Range based Cell B66  & B98
      If Sheets("sheet1").Range("B" & (nr - 2) * i + 2).Value <> "" Then
      
        Sheets("sheet2").Range("A" & nr * (i - 1) + 7 & ":H" & nr * i + 6).Copy Sheets("sheet2").Range("A" & nr * i + 7)
'       Add 34 to next row

      Else
         Exit Sub
      End If
     Next i

    Application.ScreenUpdating = True
    
End Sub

I want 2 things if anyone can added
1:this is work by run a macro, i want this is will be automated
2:i want sheet2 in this code will be hidden

 

[Unexpc]

Can anyone solve this?

 

[maabadi]

1. Are your Sheet2 before code run is hide, and after running code you want Sheet2 hide Again.
2. You can Use WorkSheet Change Event on Sheet1. Then Every time you change data in Sheet1, The code run, But your file coming up slowly.
3. Also You can use changing data in specific range. But I don't think I can do it, but can try??

 

[Unexpc]

1. Are your Sheet2 before code run is hide, and after running code you want Sheet2 hide Again.
2. You can Use WorkSheet Change Event on Sheet1. Then Every time you change data in Sheet1, The code run, But your file coming up slowly.
3. Also You can use changing data in specific range. But I don't think I can do it, but can try??

1.Yes, before and after, sheet be hidden
2.i want when B66 And next B98 and next 32 rows... fill in sheet 1 in this code, thats doing automatically copy and paste
3. Yes that is i want

 

[Fluff]

Duplicate Make automatic function formula

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).