Make automatic function formula

[Unexpc]

Hi guys
I have this code from a professional member of mrexcel, this is i have, page 1 different and page 2. I want when fill page 2 in row 73 belong print sheet and row 66 belong main sheet (that about link data from main sheet, you know it) copy page 2 and paste from cell A75 or row 75 (after page 2 without any skip row), and next page 3 fill in row 107 belong print sheet and row 98 belong main sheet, if happened doing correctly automatic drag and fill, like before copy page 2 and paste after page 3, if not, copy page 3 and paste after that, and next...
however i have this code but just doing this function by run a macro...
anyway that is automatically do without run and just doing with vba? and can anybody hide sheet2 in this code in the tab sheet??

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

 

[maabadi]

1.which Line?
2.Which Error?
3.I tested it in my Pc and it works Properly?
4.Are you change Sheet names to Your sheet Names?

 

[Unexpc]

1.which Line?
2.Which Error?
3.I tested it in my Pc and it works Properly?
4.Are you change Sheet names to Your sheet Names?

1. every i fill cell, show this error for different line
2. compile error for debug
3. so i wrong, im inserted this code you say replace in event line code, please resend code with your fix
4. no, i did what you say, but please send code

 

[maabadi]

1. Write this code and Run it:

Sub EventsOn()
Dim Chk As Boolean
Chk = Application.EnableEvents
MsgBox "Events enabled is " & Chk
If Chk = False Then
Application.EnableEvents = True
Enabled
End If
End Sub

First MessageBox Should Be "Events enabled is False"
Second MessageBox Should Be "Events enabled is True"

Then

2. Right Click on Sheet1 Tab Name and Select View Code.
Then In VBA window select Worksheet in Dropdown 1 and Change in Dropdown2 then :

Delete All Codes within code window and Paste this Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nr As Long
    Dim i As Long
    Dim Rng As Range
   
Application.EnableEvents = True
Application.ScreenUpdating = False
If Target.Column = 2 Then
Sheets("Sheet2").Visible = True

'   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
         Sheets("Sheet2").Visible = False
         Application.ScreenUpdating = True
       Exit Sub
      End If
     Next i
  Else
  Sheets("Sheet2").Visible = False
  Application.ScreenUpdating = True
    Exit Sub
  End If
End Sub

If You have Compile Error,
1.First Sure You change all sheet names to Your sheet Names.
2. Test this code on New workbook with have Data on Row 1-60 on Sheet 2. (without Hiding Sheet2)
3. Paste second Code as Before on it.
4. Save as new file as .xlsm
5. Enter Data on Sheet1 , First "B66" & Then "B98"
6. Check Are Hide Sheet2 Or Not.
7. Then If have error Take ScreenShot from Your code & Errors Occurs each Time?

 

[Unexpc]

1. Write this code and Run it:

Sub EventsOn()
Dim Chk As Boolean
Chk = Application.EnableEvents
MsgBox "Events enabled is " & Chk
If Chk = False Then
Application.EnableEvents = True
Enabled
End If
End Sub

First MessageBox Should Be "Events enabled is False"
Second MessageBox Should Be "Events enabled is True"

Then

2. Right Click on Sheet1 Tab Name and Select View Code.
Then In VBA window select Worksheet in Dropdown 1 and Change in Dropdown2 then :

Delete All Codes within code window and Paste this Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nr As Long
    Dim i As Long
    Dim Rng As Range
  
Application.EnableEvents = True
Application.ScreenUpdating = False
If Target.Column = 2 Then
Sheets("Sheet2").Visible = True

'   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
         Sheets("Sheet2").Visible = False
         Application.ScreenUpdating = True
       Exit Sub
      End If
     Next i
  Else
  Sheets("Sheet2").Visible = False
  Application.ScreenUpdating = True
    Exit Sub
  End If
End Sub

If You have Compile Error,
1.First Sure You change all sheet names to Your sheet Names.
2. Test this code on New workbook with have Data on Row 1-60 on Sheet 2. (without Hiding Sheet2)
3. Paste second Code as Before on it.
4. Save as new file as .xlsm
5. Enter Data on Sheet1 , First "B66" & Then "B98"
6. Check Are Hide Sheet2 Or Not.
7. Then If have error Take ScreenShot from Your code & Errors Occurs each Time?

i tested this, i have a problem before i say doing this correctly, every cell i fill in Column B, excel have a delay, what that cause for? this is little annoying, if it update every fill cell in column B, that range i want just B66 & B98 & next 32 Cells for recognizing and copy/paste function, if is not what cause for this delay?

 

[maabadi]

OK. Then Working.
I set target column for column B. This is for it.
I test for your cell again and if work, upload new code.
Maybe take time.
Also you can test and change this line

If Target.Column = 2 Then

To

If Target = Sheets("sheet1").Range("B" & (nr - 2) * i + 2) Then

But pasted it after

For i = 2 To 30840 Step 1

 

[maabadi]

This is Last code was working.
Because this is Worksheet Change Event, this code check if changes on the Target zone or Not. and You see Delay.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nr As Long
    Dim i As Long
   
 Application.EnableEvents = True
 Application.ScreenUpdating = False
 Sheets("Sheet2").Visible = True

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

 

[Unexpc]

That is work correctly and thank you for your solution
but i say if can, check events in specific range like B66 & B98 and...
because like your said, the worksheet will be slowly when using this code

 

[maabadi]

I remove column2 for target to the range B66 , B98 & ... at last code but it always check for change range is target range.

 

[Unexpc]

I remove column2 for target to the range B66 , B98 & ... at last code but it always check for change range is target range.

Ok Thank You for Hard Working