Loop until blank

[bi2lel]

Hello guys, hope you're doing good
So my problem is i have a task at hand with complex features or codes. I found the codes seperately and every one works good but when I try to add them together the code does not work. I want to fill a template according to ce cells and print the template and save it as pdf. and I want it to loop until a blank cell from A2,A3,A4,......., until blank. So here are my codes
Print code:

Sub print1()

'

' print1 Macro

' print 1 row without loop

'



'

Sheets("Feuil1").Select

Range("A2").Select

Selection.Copy

Sheets("canva").Select

ActiveSheet.Paste

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _

:=True, IgnorePrintAreas:=False

End Sub

the save as pdf code:

Sub SaveAsPDF()

'Saves active worksheet as pdf using concatenation

'of A2



Dim fName As String

With ActiveSheet

fName = .Range("A2").Value

.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"C:\My Documents\" & fName, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End With

End Sub

 

[mohadin]

Hi
Try

Sub Test1AND2()
    Dim fName As String, i
    With Sheets("canva")
        For i = 2 To Sheets("Feuil1").Cells(Rows.Count, 1).End(xlUp).Row
            Sheets("Feuil1").Range("a" & i).CurrentRegion.Copy .Range("A1")
            .PrintOut From:=1, To:=1, Copies:=1
            fName = .Range("A2").Value
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                 "C:\Users\ASUS\Desktop\Book1.pdf", Quality:=xlQualityStandard, _
                                 IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                                 False
        Next
    End With
End Sub

 

[bi2lel]

Hello sir, thank you for your response. So i tried your code and i had an error saying that trhis action can not be applied to merged cells

Sub Test1AND2() Dim fName As String, i With Sheets("canva") For i = 2 To Sheets("Feuil1").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Feuil1").Range("a" & i).CurrentRegion.Copy .Range("A1") .PrintOut From:=1, To:=1, Copies:=1 fName = .Range("A2").Value .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\ASUS\Desktop\Book1.pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False Next End With End Sub

 

[mohadin]

SO!,
You have merged cells witch is not recommended In VBA
Any way try this code

Sub Test1AND2()
    Dim fName As String, i
    Dim a as Variant
    With Sheets("canva")
        For i = 2 To Sheets("Feuil1").Cells(Rows.Count, 1).End(xlUp).Row
           a = Sheets("Feuil1").Range("a" & i).CurrentRegion
           .Range("A1").Resize(UBound(a), UBound(a, 2)) = a
            .PrintOut From:=1, To:=1, Copies:=1
            fName = .Range("A2").Value
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                 "C:\Users\ASUS\Desktop\Book1.pdf", Quality:=xlQualityStandard, _
                                 IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                                 False
        Next
    End With
End Sub

 

[bi2lel]

Hello sir. I used your code did not work for me. I searched more and I think this is the code that I want to work with. but it does not work. Basically I want to select first row and last row from inputmsgbox, then print a template and save it as pdf and name it after the row and loop the process until the last row selected in inputmsgbox. thank you for your responses it is very kind

Sub TestLoopPrintSavePDF()
' Print and save as PDF then Loop
Dim n As Integer, debut As Integer, fin As Integer, fName As String
    debut = InputBox("Début") ' first row to start
    fin = InputBox("Fin")     ' last row to finish
    n = debut
    Do Until n = fin
    n = n + 1
   
    Sheets("Feuil1").Select
    Range("A" & n).Select
    Selection.Copy
    Sheets("canva").Select
    Range("AD7").Select
    ActiveSheet.Paste 'Print
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
        :=True, IgnorePrintAreas:=False
   
        With ActiveSheet
    fName = .Range("AD7").Value        ' name the PDF file according to n
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\ramdane\Desktop\Archive FI 2021\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
       
       
Loop
   
   
End Sub

 

[mohadin]

Hi
It's not clear to me
can you show sample of the <<Feuil1>> and the expected what ever in <<canva>> and the file name expected to save?
Thanks

 

[bi2lel]

Hi
It's not clear to me
can you show sample of the <<Feuil1>> and the expected what ever in <<canva>> and the file name expected to save?
Thanks

Hello sir, so my objective is that my code do the following steps:
- An inputmsgBox: First row. For example, first row = 5
- An inputmsgBox: Last row. For example, last row = 10
- go to "feuil1" and copy the value of cell "A" & first row. In our example "A5"
- go to "canva" and paste it in "AD7" the template "canva" will auto populate from "feuil1"
- then print the template "canva"
- then save the template "canva" as PDF with name = the value of "AD7". In our example "5"
- repeat the process until "A" & last row. In our example "A10"
- So do the code in our example for references "A5" "A6" "A7" "A8" "A9" "A10"

 

[bi2lel]

Hello sir, so my objective is that my code do the following steps:
- An inputmsgBox: First row. For example, first row = 5
- An inputmsgBox: Last row. For example, last row = 10
- go to "feuil1" and copy the value of cell "A" & first row. In our example "A5"
- go to "canva" and paste it in "AD7" the template "canva" will auto populate from "feuil1"
- then print the template "canva"
- then save the template "canva" as PDF with name = the value of "AD7". In our example "5"
- repeat the process until "A" & last row. In our example "A10"
- So do the code in our example for references "A5" "A6" "A7" "A8" "A9" "A10"

Hello sir, so my objective is that my code do the following steps:
- An inputmsgBox: First row. For example, first row = 5
- An inputmsgBox: Last row. For example, last row = 10
- go to "feuil1" and copy the value of cell "A" & first row. In our example "A5"
- go to "canva" and paste it in "AD7" the template "canva" will auto populate from "feuil1"
- then print the template "canva"
- then save the template "canva" as PDF with name = the value of "AD7". In our example "5"
- repeat the process until "A" & last row. In our example "A10"
- So do the code in our example for references "A5" "A6" "A7" "A8" "A9" "A10"

 

[bi2lel]

View attachment 33442

 

[mohadin]

Aha
Now I can see the problem
It's merged cell not OK with VBA