Excel/formulas

Advertisement


Question
QUESTION: Damon

Hope all is good

i never heard back from you about the two programs i sent as to the slow process of updating.....can you let me know

in the meantime, i have a question, in the code below it prints out invoices, based on whether the account is active or not. It works fine, but i wanted to see what or how can i add a code if that we have a default as PDF that it prints those active invoices being printed into one folder.  right now it just prints out, and if its going to a print that's great but if it goes to a pdf default it prints one by one....

can you help please

Tony

Sub PrintMasterInvoice()

Application.ScreenUpdating = False


Dim Sh1 As Worksheet, Sh2 As Worksheet

Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, r6 As Range, r7 As Range
Dim r8 As Range, r9 As Range, r10 As Range, r11 As Range, r12 As Range, r13 As Range, r14 As Range
Dim r15 As Range, r16 As Range, r17 As Range, r18 As Range, r19 As Range, r20 As Range, r21 As Range
Dim r22 As Range, r23 As Range, r24 As Range, r25 As Range

Dim s1 As Range, s2 As Range, s3 As Range, s4 As Range, s5 As Range, s6 As Range, s7 As Range
Dim s8 As Range, s9 As Range, s10 As Range, s11 As Range, s12 As Range, s13 As Range, s14 As Range
Dim s15 As Range, s16 As Range, s17 As Range, s18 As Range, s19 As Range, s20 As Range, s21 As Range
Dim s22 As Range, s23 As Range, s24 As Range, s25 As Range


Dim ce As Range, I As Long
Dim bk1 As Workbook
Set bk1 = ThisWorkbook
bk1.Activate
    
Sheets("pymtlog").Visible = True
Sheets("monthlystatement1").Visible = True

' define your sheets outside the loop
Set Sh1 = bk1.Worksheets("pymtlog")  '<== Moved
Set Sh2 = bk1.Worksheets("monthlystatement1")  '<== Moved

' you find the bottom of your data using row M

For I = 8 To Sh1.Range("B65536").End(xlUp).Row


' but your check for your value in column "I"
' if that is correct OK.  But if you want to check in ' column M change your 9 to "M" or 13

If ((Cells(I, 2).Value = Sh1.Range("A3"))) Then

Set r1 = Sh1.Cells(I, "E") 'Account #
Set r2 = Sh1.Cells(I, "D") 'Name
Set r3 = Sh1.Cells(I, "Z") 'Address
Set r4 = Sh1.Cells(I, "ASE") ' City, St Zip
Set r5 = Sh1.Cells(I, "O") ' Home #
Set r6 = Sh1.Cells(I, "M") ' Cell #
Set r7 = Sh1.Cells(I, "P") ' Email Addy
Set r8 = Sh1.Cells(I, "AE") ' Date Closed
Set r9 = Sh1.Cells(I, "AK") ' Payment
Set r10 = Sh1.Cells(I, "AJ") ' Terms
Set r11 = Sh1.Cells(I, "AI") ' Rate
Set r12 = Sh1.Cells(I, "AL") ' 1st Pymt
Set r13 = Sh1.Cells(I, "ASD") ' Balance
Set r14 = Sh1.Cells(I, "ASF") ' Last Paid

Set r15 = Sh1.Cells(I, "ASG") ' Amt Paid
Set r16 = Sh1.Cells(I, "ASH") ' Months Left
Set r17 = Sh1.Cells(I, "AQ") ' Year
Set r18 = Sh1.Cells(I, "AP") ' Make
Set r19 = Sh1.Cells(I, "AS") ' Serial Number

Set r20 = Sh1.Cells(I, "ASJ") ' Applied to Interest
Set r21 = Sh1.Cells(I, "ASK") ' Applied to Principal
Set r22 = Sh1.Cells(I, "ASD") ' Current Balance
Set r23 = Sh1.Cells(I, "ASI") ' Next Date Due
Set r24 = Sh1.Cells(I, "AK") ' Amount Due
Set r25 = Sh1.Cells(I, "ASL") ' Months Past Due



Set s1 = Sh2.Range("M1") 'Account #
Set s2 = Sh2.Range("M2") 'Name
Set s3 = Sh2.Range("M3") 'Address
Set s4 = Sh2.Range("M4") ' City, St Zip
Set s5 = Sh2.Range("M5") ' Home #
Set s6 = Sh2.Range("M6") ' Cell #
Set s7 = Sh2.Range("M7") ' Email Addy
Set s8 = Sh2.Range("M8") ' Date Closed
Set s9 = Sh2.Range("M9") ' Payment
Set s10 = Sh2.Range("M10") ' Terms
Set s11 = Sh2.Range("M11") ' Rate
Set s12 = Sh2.Range("M12") ' 1st Pymt
Set s13 = Sh2.Range("M13") ' Balance
Set s14 = Sh2.Range("O1") ' Last Paid

Set s15 = Sh2.Range("O2") ' Amt Paid
Set s16 = Sh2.Range("O3") ' Months Left
Set s17 = Sh2.Range("O4") ' Year
Set s18 = Sh2.Range("O5") ' Make
Set s19 = Sh2.Range("O6") ' Serial Number

Set s20 = Sh2.Range("O7") ' Applied to Interest
Set s21 = Sh2.Range("O8") ' Applied to Principal
Set s22 = Sh2.Range("O9") ' Current Balance
Set s23 = Sh2.Range("O10") ' Next Date Due
Set s24 = Sh2.Range("O11") ' Amount Due
Set s25 = Sh2.Range("O14") ' Months Past Due


s1.Value = r1.Value
s2.Value = r2.Value
s3.Value = r3.Value
s4.Value = r4.Value
s5.Value = r5.Value
s6.Value = r6.Value
s7.Value = r7.Value
s8.Value = r8.Value
s9.Value = r9.Value

s10.Value = r10.Value
s11.Value = r11.Value
s12.Value = r12.Value
s13.Value = r13.Value
s14.Value = r14.Value
   
s15.Value = r15.Value
s16.Value = r16.Value
s17.Value = r17.Value
s18.Value = r18.Value
s19.Value = r19.Value
s20.Value = r20.Value
s21.Value = r21.Value
s22.Value = r22.Value
s23.Value = r23.Value
s24.Value = r24.Value
s25.Value = r25.Value



Sh2.PrintOut
' since I reference sh2 directly, I don't have ' to hide sh1 and then show it again sh2.Printout


     lOrders = lOrders + 1


End If

Next I '<== added command
Sh2.Visible = xlSheetHidden
Set Sh1 = Nothing
Set Sh2 = Nothing

 MsgBox lOrders & "  - Monthly Statements Printed"
       success.Show
   
   
Application.ScreenUpdating = True


End Sub

ANSWER: Hi again Tony,

I recommend that rather than printing as pdf you use ExportAsFixedFormat in place of the PrintOut method:

Sh2.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard

To set the folder where the pdf files get saved you can set the working folder before doing the saves with a chdir statement as in this example:

  ChDir "C:\Users\Anthony\SkyDrive\Documents\Tony\"

Damon

---------- FOLLOW-UP ----------

QUESTION: ok but consider this stupid but where do i put that last state Chdir?

is it like this?

Sh2.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard,ChDir "C:\Users\Anthony\SkyDrive\Documents\Tony\"

or

ChDir "C:\Users\Anthony\SkyDrive\Documents\Tony\",
Sh2.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard

Answer
Hi again Tony,

Sorry for the lack of clarity.  It should be the latter.

ChDir "C:\Users\Anthony\SkyDrive\Documents\Tony\"
Sh2.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard


Regarding the question regarding the slow recalculating upon opening, I looked at this a week ago but at that time was unable to duplicate the problem, perhaps because I don't have all the files needed to provide the linked values necessary to cause calculation.  I'll take another look and see if I think that is indeed the problem.


Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.