Loop until blank

bi2lel

New Member
Joined
Feb 28, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA 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:
VBA 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
 
Last edited by a moderator:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
Try
VBA Code:
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

New Member
Joined
Feb 28, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    127.9 KB · Views: 6

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
SO!,
You have merged cells witch is not recommended In VBA
Any way try this code
VBA 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

New Member
Joined
Feb 28, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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
 
Last edited by a moderator:

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Feb 28, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 28, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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"
canva.jpg
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Aha
Now I can see the problem
It's merged cell not OK with VBA
 

Watch MrExcel Video

Forum statistics

Threads
1,129,805
Messages
5,638,468
Members
417,026
Latest member
UDK

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