Copy 5 rows from one sheet and paste in another sheets based and save as PDF based on condition

[Jyotirmaya]

I have data in Sheet1 in Column A & B, I want 5 Rows from Sheet 1 to copy into Sheet 2.

For example I want the data from Sheet 1 A1:A5 to copy into Sheet 2 A8 to A12 and B1:B6 to copy into C8 to C12 of Sheet 2 and save the page as PDF in desktop then continue the process till last row of Sheet1, 5 rows of Sheet 1 to be pasted by replacing existing data and save as PDF then again next 5 rows & so on...I have data till 1500 in Sheet 1 so it should save as PDF for 300 times after copying the data, The saveas PDF file name may be saved as 1,2,3,etc

Book1
	A	B
1	Apple	Orange
2	Car	Bus
3	Red	Green
4	Sky	Star
5	Tiger	Lion
6	1	2
7	3	4
8	5	6
9	7	8
10	9	10
11	A	B
12	C	D
13	E	F
14	G	H
15	I	J
16	Apple	Orange
17	Car	Bus
18	Red	Green
19	Sky	Star
20	Tiger	Lion
21	11	12
22	13	14
23	15	16
24	17	18
25	19	20
26	A	B
27	C	D
28	E	F
29	G	H
30	I	J
Sheet1

Book1
	A	B	C
1	A	b	c
2
3
4
5
6
7
8		d
9
10
11
12
Sheet2

Please help me a excel Macro code for that.

Book1
	A	B	C
1	A	b	c
2
3
4
5
6
7
8	Apple	d	Orange
9	Car		Bus
10	Red		Green
11	Sky		Star
12	Tiger		Lion
Sheet2

Cell Formulas
Range	Formula
A8:A12	A8	=Sheet1!A1
C8:C12	C8	=Sheet1!B1

 

[John_w]

Try this macro:

Public Sub Create_PDFs()

    Dim DesktopFolder As String
    Dim r As Long
    
    DesktopFolder = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
    
    With Worksheets("Sheet1")
        For r = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row Step 5
            .Cells(r, "A").Resize(5).Copy Worksheets("Sheet2").Range("A8:A12")
            .Cells(r, "B").Resize(5).Copy Worksheets("Sheet2").Range("C8:C12")
            Worksheets("Sheet2").ExportAsFixedFormat Type:=xlTypePDF, Filename:=DesktopFolder & (r + 5) \ 5 & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Next
    End With
    
End Sub