Insert page automatically

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
490
Office Version
  1. 2019
Platform
  1. Windows
Hi guys
i have a print sheet from my main worksheet and i linked main sheet cells to this print sheet. i want when a page fill, next page automatically insert copy a page from previous page with data and format cells and when next page fill, that like before, in say like insert page but automatically, have any idea to do this?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
490
Office Version
  1. 2019
Platform
  1. Windows
i have page 1 diffrent 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...
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
For example:
1. what is your criteria for Run filling Code: A73 at Print Sheet or A66 at Main Sheet?
1. At sheetP , page 2, if cell A42 fill then copy range (A35:H66) from main sheet to range (A42:73)? If I wrong correct it.

Also tell example range for page 3 and 4 to I write the code. (minimum 3 example for different Pages)
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
For example This code if sheet2 isn't hidden, Copy from A67:H98 from sheet1 to A75:H106 at Sheet2 if A66 at Sheet1 fill with data and .... :
VBA Code:
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 A66  & A 98
      If Sheets("sheet1").Range("A" & (nr - 2) * i + 2).Value <> "" Then
      
        Sheets("sheet1").Range("A" & (nr - 2) * i + 3 & ":H" & (nr - 2) * i + 34).Copy Sheets("sheet2").Range("A" & nr * i + 7)
'       Add 34 to next row
        Debug.Print Range("A" & (nr - 2) * i + 2).Address
        Debug.Print Range("A" & nr * i + 7).Address
      Else
         Exit Sub
      End If
     Next i

    Application.ScreenUpdating = True
    
End Sub

Please answer to previous questions very Correctly.
 

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
490
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

For example:
1. what is your criteria for Run filling Code: A73 at Print Sheet or A66 at Main Sheet?
1. At sheetP , page 2, if cell A42 fill then copy range (A35:H66) from main sheet to range (A42:73)? If I wrong correct it.

Also tell example range for page 3 and 4 to I write the code. (minimum 3 example for different Pages)
1.A73 have a formula that linked to B66
2.at sheetP(print sheet) when page 2 in print sheet or Row 66 fill in main sheet, copy page 2 from print sheet and paste after page 2 in print sheet, not copy from another sheet, and next page when fill,copy page 3 and paste after that and next...
And another thing, i want hidden sheetP anyway
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
1. Then You want copy and Paste formula not value. Is it correct?
2. what is exactly Range for copy from page 2?
3. What is exactly Range for Paste to Page 3?
4. How many row between copy and pasted region , we have ?
5. one more example for Page 4 also?
 

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
490
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

1. Then You want copy and Paste formula not value. Is it correct?
2. what is exactly Range for copy from page 2?
3. What is exactly Range for Paste to Page 3?
4. How many row between copy and pasted region , we have ?
5. one more example for Page 4 also?
1.no, i want copy and paste with formula on each cells(for example when copy page 2 and paste after that, drag and fill will be automate do,thats i want)
2.A41:H74
3.A75:H108
4. I don't understang exactly, but if you mean skiping cell or anything, no, that not have space or blank cell
5.when A107 in print sheet or B98 in main sheet is fill, copy range A75:H108 (page 3) and pasted in A109:H142 (that been page 4)
And if can do, you consider Fill A73 (in page 2 ), A107 (in page 3), A141 (in page 4) and... fill in print sheet not in main sheet for when it doing copy and paste (this cells linked to main sheet, ofcourse if when in main sheet fill, this cell be activated, do this, if not consider Cell main sheet (Row 66 for copy page 2 and paste after that, Row 98 for copy page 3 and paste after that, Row 130 for copy page 4 and paste after that and next...)
If you want i send this numbers squence
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
OK. then you want if you fill B66 at Main sheet then copy A41:H74 to A75:H108 and ... . Is it correct?
if it is correct this is code. change sheet1 to main sheet name and sheet2 to print sheet name.
VBA Code:
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
 
Solution

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
490
Office Version
  1. 2019
Platform
  1. Windows
OK. then you want if you fill B66 at Main sheet then copy A41:H74 to A75:H108 and ... . Is it correct?
if it is correct this is code. change sheet1 to main sheet name and sheet2 to print sheet name.
VBA Code:
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
Correct, this worked, anyway that can do automatically? without run a macro... and set this vba? and hiddem from tab sheet?? Thanks for professional
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,495
Latest member
Ragamacam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top