I'm looking for a way to save it as a separate file for each page in Excel.

How do I save a separate file for each sheet in Excel?
https://incloop.com/excel%E3%81%A7%E3%82%B7%E3%83%BC%E3%83%88%E6%AF%8E%E3%81%AB%E5%88%A5%E3%83%95%E3%82%A1%E3%82% A4% E3% 83% AB% E3% 81% AB% E3% E3% 81% E3% E3% E3% E4% BF% 9D% E5% AD% 98% E3% 82% E3% 8B% E6% E6% E6% B3% 95/a>

2022-09-30 20:22

1 Answers

You can get page breaks in HPageBreak and VPageBreak.
I think we can achieve our goal by converting the sample code at the link you asked into the logic of creating a file for each page break.

In addition, this response was based on QA site.
Here are some helpful answers.

If you really want to get a new page position, why don't you use the location properties of the HPageBreak object?
HPageBreaks (a collection of HPageBreak objects)
ActiveSheet.HPageBreaks.Count
ActiveSheet.HPageBreaks(1).Location.Row (line of first page break)
Location.Row knows the line of page break.

Below is a sample code that divides the output destination by page feed and outputs it with a file name with page feed coordinates, such as Sheet1_2_3.xlsx.
If used as it is, the reference to the formula will be lost or the coordinates will be shifted.
The behavior of the inserted object is also unconfirmed.
Therefore, please note that we cannot guarantee the contents of the output source.

If necessary, try to modify it to meet your requirements, such as creating a work sheet that holds cell values in value format.

Sub SheetSave()
    Dim wb1 As Workbook
    Set wb1 = ActiveWorkbook
    'Repeat as many sheets as possible
    Fori=1Towb1.Sheets.Count
        Set mySheet=wb1.Works(i)
        Dim r1, r2, c1, c2 As Integer
        US>'Repeat the number of rows to be split into pages
        r1 = 1
        Forx=0 To mySheet.HPageBreaks.Count
            c1 = 1
            If x = mySheet.HPageBreaks.Count Then
                r2 = mySheet.UsedRange.Rows.Count+1
            Else
                r2 = mySheet.HPageBreaks(x+1).Location.Row
            End If
            US>'Repeat the number of columns to be split into pages
            Fory=0 To mySheet.VPageBreaks.Count
                Ify=mySheet.VPageBreaks.Count Then
                    c2 = mySheet.UsedRange.Columns.Count+1
                Else
                    c2 = mySheet.VPageBreaks(y+1).Location.Column
                End If
                'Save as sheet name_x_y.xlsx
                DimfName As String
                fName=mySheet.Name&"_"&CStr(x)&"_"&CStr(y)&".xlsx" 
                With Workbooks.Add
                    mySheet.Range(mySheet.Cells(r1,c1), mySheet.Cells(r2-1,c2-1)).Copy Destination: =.Sheets(1).Range("A1")
                    .SaveAsFilename:=fName
                    .Close
                End With
                c1 = c2
            Nexty
            r1 = r2
        Next x
    Next i
End Sub

2022-09-30 20:22

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656