SOLVED

How To - 1 Worksheet "gets" the data, another worksheet displays it for printing.

Copper Contributor

Hello - I have a problem, and I'm not sure how to make it easier for the "end user". I have a workbook with a "Packing Slip" template in worksheet 1, it's specific to a customer and we put it in excel for formatting ease and a few other reasons. In Worksheet 2 I have an ODBC Connection to a database and it lays out the fields such as customer name, address, qty ordered, item ordered, etc. Each order is on it's own "Row" in worksheet 2. 

 

In worksheet 1, the order # field, I have setup to go to worksheet 2, and import that field, and then I have to go to all the other fields that are being imported, and make sure they are setup on the right line for the order I'm working with. Typically they are so it's not an issue. 

 

Here's the question: How can I get worksheet1 to go through worksheet2, updating the fields into worksheet1 and then print worksheet1, and then go to the next record in worksheet2, import that information, then print worksheet1, then repeat the process with going to the next record in worksheet2, and printing worksheet1 until there are no more records in worksheet2?

 

I hope I've explained this well enough. If you have any questions, please let me know and I'll do my best to answer them. Thank you in advance.

6 Replies
best response confirmed by Jeremy_82 (Copper Contributor)
Solution

@Jeremy_82there may be a couple of solutions but the best I can say is make all fields on sheet 1 look up the correct value on sheet 2 based on the order #.  Then have a macro loop through all the order numbers, update sheet 1, and print that sheet.

here is sample VBA code, a mix of code I already have/use and some that I whipped up for you.  It should work:

Sub Print_All()

  dim invoiceNumber, invoiceRange as range

 set invoiceRange = Worksheet2.range("range of invoice numbers")

  For each invoiceNumber in invoiceRange

    Worksheet1.range("invoce number cell") = invoiceNumber.value

    call Print2PDF_Click()

  next

end sub

 

Sub Print2PDF_Click()
    Dim InvoiceDate
    Dim fname
    InvoiceDate = ActiveSheet.Cells(4, ActiveSheet.Range("$1:$10").Find("Invoice Date").Column + 1)
    fname = ActiveWorkbook.Path
    fname = CleanFileName(fname)
    fname =  fname & "_" & Date$
    Dim lastrow
    Dim lastcol
    lastrow = ActiveSheet.Range("$a:$a").Find("Grand Total", , xlValues).Row()
    lastcol = ActiveSheet.Cells(1, 1).Offset(0, 1).Column()
    With ActiveSheet.PageSetup
        .PrintArea = "$a$2:$" & Chr(95 + lastcol) & "$" & lastrow
        .Zoom = False
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    On Error GoTo FileDialog
    Call ActiveSheet.ExportAsFixedFormat(xlTypePDF, fname, xlQualityStandard, True, False, 1, , False)
    Exit Sub
FileDialog:
    fname = InputBox("There was a problem saving the file.  Please make sure the path exists " & _
        "and the filename does not have any illegal characters (<,>,:,"",/,\,|,?,*). " & _
        "Please review and enter a corrected name & path for the file", "Filename/Path Error", fname)
    On Error GoTo FileDialog
    If fname <> "" Then
        Resume
    End If
End Sub

Function CleanFileName(fname, Optional inchar = "-")
'This function will replace bad characters in a filename with inchar
    Dim badchars
    badchars = Array("<", ">", ":", """", "/", "\", "|", "?", "*")
    For Each badc In badchars
        fname = Replace(fname, badc, inchar)
    Next
    CleanFileName = fname
End Function

@mtarler I'm new when it comes to VBA. I'm not sure what I'm supposed to do or where to put your code. I put it into sheet1 I believe. I've attached the file that I'm trying to do this with. Maybe you can guide me on what to change in the code to make this work? 

@Jeremy_82  I completely updated the sheet:

a) I created data validation on PO number to include the PO numbers on sheet 2

b) I created an "index value" in cell B4 to use for all the other cells (note I chose B4 so you could move the Disney Logo back over top of it and hide it, but you could also just make the font white)

c) I updated fields to refer to the corresponding table column and index value

d) I fixed your if(if(if(if(…. statement using a simple vlookup

e) I made a number of fixes in the macro to fix minor bugs and work explicit for your sheet

f) the macros were moved to module1 as that is more appropriate location

 

You can test is by clicking 'view' tab and click on show macros and then run 'PDFall'

NOTE this does not change the Account Number field as you didn't have any info for what that should be.

You may also want to go into the macro and change the name of the file and possibly many other things when you apply it to your actual sheet.

 

@mtarler Wow you did not have to do all of that. That is awesome!! Your macro does loop through the records and tries to print it, but I can't get the print area defined, and it's cutting off some of the packing slip. I'm going to keep playing with it and see if I can at least get the page to "print" properly, and then I'll try to figure out how to actually get it to print. But thank you so much!! You've been a great help!

@Jeremy_82  you're welcome.  as I noted, I happen to have a lot of it done in a similar project so I just had to tweak various parts for you.  in order to change the print area you will need to go into the macro because I define the print area in there.  If I recall correctly I use the usedrange property so not sure why it is clipping, but you can just manually replace the range with a fixed range that works for you.

@mtarler Yes thank you, I did make it a fixed range, and I got it all to work. Thank you again!! That IF statement was nasty, I'm going to have to dig more into VLOOKUP function to make it easier for me, I greatly appreciate your assistance!!

1 best response

Accepted Solutions
best response confirmed by Jeremy_82 (Copper Contributor)
Solution

@Jeremy_82there may be a couple of solutions but the best I can say is make all fields on sheet 1 look up the correct value on sheet 2 based on the order #.  Then have a macro loop through all the order numbers, update sheet 1, and print that sheet.

here is sample VBA code, a mix of code I already have/use and some that I whipped up for you.  It should work:

Sub Print_All()

  dim invoiceNumber, invoiceRange as range

 set invoiceRange = Worksheet2.range("range of invoice numbers")

  For each invoiceNumber in invoiceRange

    Worksheet1.range("invoce number cell") = invoiceNumber.value

    call Print2PDF_Click()

  next

end sub

 

Sub Print2PDF_Click()
    Dim InvoiceDate
    Dim fname
    InvoiceDate = ActiveSheet.Cells(4, ActiveSheet.Range("$1:$10").Find("Invoice Date").Column + 1)
    fname = ActiveWorkbook.Path
    fname = CleanFileName(fname)
    fname =  fname & "_" & Date$
    Dim lastrow
    Dim lastcol
    lastrow = ActiveSheet.Range("$a:$a").Find("Grand Total", , xlValues).Row()
    lastcol = ActiveSheet.Cells(1, 1).Offset(0, 1).Column()
    With ActiveSheet.PageSetup
        .PrintArea = "$a$2:$" & Chr(95 + lastcol) & "$" & lastrow
        .Zoom = False
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    On Error GoTo FileDialog
    Call ActiveSheet.ExportAsFixedFormat(xlTypePDF, fname, xlQualityStandard, True, False, 1, , False)
    Exit Sub
FileDialog:
    fname = InputBox("There was a problem saving the file.  Please make sure the path exists " & _
        "and the filename does not have any illegal characters (<,>,:,"",/,\,|,?,*). " & _
        "Please review and enter a corrected name & path for the file", "Filename/Path Error", fname)
    On Error GoTo FileDialog
    If fname <> "" Then
        Resume
    End If
End Sub

Function CleanFileName(fname, Optional inchar = "-")
'This function will replace bad characters in a filename with inchar
    Dim badchars
    badchars = Array("<", ">", ":", """", "/", "\", "|", "?", "*")
    For Each badc In badchars
        fname = Replace(fname, badc, inchar)
    Next
    CleanFileName = fname
End Function

View solution in original post