• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for looping, printing and saving to pdf network directory

Annette Powell

New Member
Hello, I have a macro that loops thru a sheet [KEY STATS], entering the name into the sheet names [23 Score] and then populates with the lookups for the individual. Then it will print to a printer and all is good. Using the same technique, i need to be able to run my macro and instead of printing to a local printer, i need to print to a pdf file and have it save, naming it from where it pulls the names in from the loop but every variation I try to use when printing errors out. Any help or suggestions out there? TIA (sample attached).
 

Attachments

  • Book1.xlsx
    36.5 KB · Views: 3
Hello, as your sample can not contain any VBA code so you should better attach the workbook with your 'macro' …​
 
Do you need a complete VBA code populating Key Stats data to 23 Score - so you must well describe each step -​
or just a mod for the 23 Score VBA procedure CommandButton1_Click ? (Where the source worksheet name is wrong)​
 
i think i just need a modification. What i have in there already works to print to the printer. I just need to add or change the code so instead of printing to the printer, it will print a pdf and save to a network directory.
 
Actually the VBA procedure CommandButton1_Click creates a pdf file but​
you must delete the useless Select codeline and replace both ActiveSheet with Sheet1 …​
 
Sorry Marc, Im not following. the tab for 23 score has to be printed for about 400 folks so the macro that is attached does that by looping thru the names on Key stats tab and then printing to a printer. I have lookups on the 23score so as the names change, so does the info. Then I would take the printed sheets and scan them in and get a large pdf. Now, the powers that be, want a pdf sheet for each person (using the persons name as the name of the pdf). I can essentially do it the hard and painstakingly long way by separating the pdfs and naming each one individually but i dont know how to put that in a macro. It sounds like you are telling me to create a button to handle it (which i know how to create a button) but I am lost as to the code that goes behind it. Hope that makes sense and I appreciate the help.
 
I'm coming to this late, Annette, but I think you're saying you've already conquered the part about exporting the file and printing it one time—but that you want your program to run down a list of 400 names in the "Key Stats" worksheet and for each name a) export the PDF to a file named after that attorney and b) print it. For that you want to do the same thing you're already doing, but do it inside a loop that looks at each row in the worksheet. It might look something like this:
Code:
' Set up all the stuff that'll be the same for each attorney.
Sheets("Sheet1").Select
ActiveSheet.PageSetup.PrintArea = "B7:E17"
fnp = Application.DefaultFilePath & "\" 'or whatever you want for the output path

' Loop through all the names in the Key Stats worksheet.
Set ows = ThisWorkbook.Worksheets("Key Stats")
Set ocs = ows.Cells
rZ = 'last row - figure out whatever that is.  There are various methods; I'll let you struggle with this a bit
For jr = 4 to rZ
  Atty = ocs(jr, 2).Value 'get the name
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fnp & Atty & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True _
    , IgnorePrintAreas:=False, OpenAfterPublish:=False
  next jr
Some comments about this:
1) I haven't tested this code; I leave that to you.
2) I changed OpenAfterPublish to False; presumably you don't want to open 400 identical PDFs.
3) I don't see anything here about printing; does that happen automatically during the export, by some method?
4) Do you really want the 400 files to be identical except for the filename? If there are supposed to be differences in the content, I don't see how that's done in your code so far.
 
Bob, thanks for your comments. I was able to get yours to work and it does what I expect it to - loop thru every name in key stats and print to pdf. On #4, no, i dont want it to print the same sheet for every name. I had a macro that loops thru the key stats and returns the name to the 23 score sheet and then it changes the values based on the persons name (thru lookups). When I run them together, it isnt going thru the steps together. It cycles thru mine and goes thru each iteration of changing the names and then it runs yours and saves a file for each person (but the data is the same. I dont think I can have 2 subs in a macro, correct? What I am trying to accomplish is to change the name on the 23 score and then print that file to pdf. Goes to the next name, prints to pdf etc. until the end of range.
I appreciate your help - I am very close but cant pull everything together to work. Thank you
 
Sorry for the delay, Annette: My day job kept me busy today.

Let's look at that two-subs-in-a-macro question. It may just be a matter of terminology, so I'll start with that, and if I end up telling you things you already know, please forgive me. Folks often use different words for these things, but here are the terms as the Microsoft documentation uses them for Excel:

Project: All the code in one Excel workbook. Some purists may insist that "project" includes the worksheets themselves, too, and maybe they're right, but we'll stick to the VBA aspect here.

Module: In the VBA Editor, a "module" looks like a page where you put VBA statements. There's more than one kind of module, but let's leave that for later, if we get that far.

When you're writing in normal code modules you can write Subs (short for "subroutines") or "Functions". The difference is that a Sub runs and does things; a Function can do the same, but it returns a value. For example, I can write a Sub that looks like this:
Code:
Sub Main()
  MsgBox "Hi, there!"
  End Sub
That sub just displays a message. But a Function looks like this:
Code:
Function Bob(Value)
  Select Case True
    Case Value > 0: Bob ="positive"
    Case Value < 0: Bob = "negative"
    Case Else: Bob = "zero"
    End Select
  End Function
Notice that by the end of running Bob, there's a variable by the same name ("Bob") that is assigned a value. So another subroutine or function can "call" Bob and get back a character string "positive", "negative" or "zero":
Code:
MsgBox xyz & " is " & Bob(xyz) & "."
If xyz is 33, then what you see is "33 is positive.", you see.

Each module can contain one or many Subs or Functions. So yes, you can have more than one Sub in a module. But you asked whether you can have more than one in a macro, and I'm not sure what you meant, hence this uninvited terminology lesson.

Let's pretend that the word "macro" means a single Sub or Function. (Why not?) In that case, a macro cannot have Subs or Functions within it—exactly. But it can call other Subs and Macros, and frequently does. If that gives you ideas, good. If you want to, you can combine two tasks in one loop like this:
Code:
For jr = 4 to LastRow
  ChangeName
  PrintPDF
  Next jr
In the ChangeName subroutine you do your task, and it PrintPDF you do the other.

Sometimes, if the two subs are long and complicated, it's helpful to do that. But in this case I don't see the need for that. Why not just do both tasks right there inside the same loop?

If that doesn't give you the ideas you need, show me what you're doing when you say "change the name". What does your code look like now?
 
Bob, Thank you for the lesson - appreciate that. I dont work with macros or vba enough to get it right so I am struggling. Here is my code right now. The first part, "Dim cell as Range" is what I originally had and it would loop thru the names and populate the spreadsheet with an id below (HLL), which in turn populated the spreadsheet with that persons personal stats (via lookups) and then print to a printer. I would then scan to a pdf and then separate into individual files. I have tried unsuccessfully to change my code to do the same thing, populate the id field and then print to a pdf using the name as the filename. Could never get it to work so I finally reached out for help. Looking at the image below that I supplied, i want to populate the field (HLL) [comes from KEY STATS] which would have to loop through all of the names and then print to a pdf, using the name as the file name. I hope that makes sense. TYIA

>>> use code - tags <<<
Code:
' Macro1 Macro
'
Dim cell As Range
For Each cell In Sheets("Key Stats").Range("A4:A" & Rows.Count) _
.SpecialCells(xlCellTypeConstants)
Worksheets("23 score").Range("d1").Value = cell.Value      (populating D1 with id so that the spreadsheet populates with the persons data)

ActiveSheet.PageSetup.PrintArea = "B1:t53"
'fnp = Application.DefaultFilePath & "\" 'or whatever you want for the output path

fnp = "f:\team\000999.05600_acct\annette\2023\fy 2023" & "\" 'or whatever you want for the output path

' Loop through all the names in the Key Stats worksheet.
Set ows = ThisWorkbook.Worksheets("Key Stats")
Set ocs = ows.Cells
rZ = 14    'last row - figure out whatever that is.  There are various methods; I'll let you struggle with this a bit
For jr = 4 To 6
  Atty = ocs(jr, 2).Value 'get the name
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fnp & Atty & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True _
    , IgnorePrintAreas:=False, OpenAfterPublish:=False
  Next jr

'ActiveSheet.PrintOut Copies:=1, Collate:=True
Next cell

End Sub


84355
 
Last edited by a moderator:
I'm slowly catching on. I didn't ask before, but I guess you have two worksheets. "Key Stats" contains a bunch of names, and "23 score" is the sheet you want to modify and export (and maybe print), once for each row in "Key Stats".

So far we've been talking only about the attorney's name, but now that I see the screen shot I'm guessing that "Key Stats" has not only the name but the other information too: department, title and goals for 2023 and 2022. You want to update all of those fields before exporting (and/or printing). Right?

So let's look at the program you have so far, and add to it a bit:
Code:
Sub Something() 'I don't care what you call this routine

' You have two sheets, one incoming and one outgoing - so to speak - so instead of having to spell them out all the way through, let's
' set some variables to make it easier.
Set owsFm = ThisWorkbook.Sheets("Key Stats") '"ows" just means a worksheet object to me
Set owsTo = ThisWorkbook.Sheets("23 score") 'I'm just assuming both worksheets are in the same workbook
Set ocsTo = owsTo.Cells 'ocs is my way of talking about cells; you can stick to Range if you want
owsTo.PageSetup.PrintArea = "B1:T53"
fnp = "f:\team\000999.05600_acct\annette\2023\fy 2023" & "\" 'we'll set this outside the loop; it's the same for all of them, right?

For Each cell In owsFm.Range("A4:A" & Rows.Count).SpecialCells(xlCellTypeConstants)
  ' This is clever!  I've never used this before; I would have ascertained the last row and then just looked though them all, as in my
  ' previous example.  But I'm not sure why you want just the constants.  Guess it's safe enough, though.

  jr = cell.Row 'we still need the row number, though
  Atty = owsFm.Range("B" & jr).Value 'get the name
  ocsTo(<rownum>, <colnum>).Value = Atty 'insert the attorney name in the score sheet
  ' get the rest of the values from this row and assign them to the appropriate cells in the score sheet
  owsTo.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fnp & Atty & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True _
    , IgnorePrintAreas:=False, OpenAfterPublish:=False
  owsTo.PrintOut Copies:=1, Collate:=True
  Next cell
  End Sub
I am not claiming this is ready to run; just look at it and see what I added. Then fix it so it uses variable names that you like to use, and other changes you think appropriate. Also fill in the bits where you put the other profile data in the sheet to be printed. Then we'll talk again, if you're not satisfied.

About designating cells: I'm a programmer from way back, and I find it naturally to use numbers for both rows and columns. Excel can handle that; You can just say "Sheet13.Cells(1, 5)" to refer to Sheet13!E1. (Row first, then column, in that format.) But you're using letters and the Range function, and that's usually fine.
 
Bob, thank you so much for this. I had to change a couple things but I have tested it and it works like a charm. Thank you for being so patient. Appreciate it. have a good one.
 
Back
Top