Pulling custom footer from another sheet, to only appear on *final* page

[PureBluff]

Hi all,

I'm fairly new to VBA but was enlisted with a task to help work out, recently. With a bit of assistance from people who knew a lot more than me, we've been able to muster up a working document, but I require a 'checkbox / comments box' at the bottom of every finished document - we're printing up to 300 of these a day.

I've come across some code that should work to allow it to only print on the final page;

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim TotPages As Long
    TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    If TotPages > 1 Then
        With ActiveSheet.PageSetup
            .CentreFooter = ""
            ActiveSheet.PrintOut From:=1, To:=TotPages - 1
            .CentreFooter = Range("").Value
            ActiveSheet.PrintOut From:=TotPages, To:=TotPages
        End With
    End If
End Sub

However, this doesn't seem to let me pull from sheet4 using the code I was trying.

Here's the current loop we're using to pull the data from another spreadsheet

Sub GetData()
    sr = 2  'start row source
    cc = 14  'no of columns to copy from source
    dr = 11
    source_file = ActiveWorkbook.Path & "\" & "filename.xls"
    Sheet1.Select   'MAKE SURE YOU SELECT DESTINATION BEFORE RUNNING!!!!
    dest_sheet = ActiveWorkbook.Name
    If Dir(source_file) = "" Then
        MsgBox "Cant find source file?" & vbCr & vbCr & source_file _
        & vbCr & vbCr & "Will use Sheet2 as source instead"
        Set ws = Worksheets("Sheet2")
    Else
        Workbooks.Open source_file      'load source workbook - YOU DONT HAVE TO!!!
        Set wb = Workbooks(ActiveWorkbook.Name)
        Set ws = wb.Worksheets(1)
    End If
    Workbooks(dest_sheet).Activate
    clearrow = dr
    Do                      'clear content
        Rows(clearrow).Clear
        clearrow = clearrow + 1
    Loop Until Cells(clearrow, 1) = ""
    'Stop    'to debug
    Application.ScreenUpdating = False 'speeds up code!
    Do                      'copy content from sheet2
        For i = 1 To cc
            Cells(dr, i) = ws.Cells(sr, i)
            Cells(dr, i).Borders.LineStyle = xlContinuous
        Next i
        sr = sr + 1
        dr = dr + 1
    Loop Until ws.Cells(sr, 1) = ""
    Set ws = Nothing
    On Error Resume Next    'stops error on wb.close if using sheet2
    wb.Close    'close source file
    Set wb = Nothing
    Application.ScreenUpdating = True
    Sheets("Sheet1").Range("I2:J3").ClearContents 'Clears Cell I1 Customer upon import
End Sub

Any insight on how I can pull the data from Sheet4?

I've also tried doing it as an image (attached image in case there is an alternative suggestion based on the fact it's a table)

Thanks in advance.

i.imgur.com/GwzbrZA.png

[SamT]

Sorry to be so long getting back to you, I don't always see every post made if they fall oof the first page of the forum. If you don't see your last post on the first page, you are allowed to post a "Bump."

The reason none of the 60 viewers have answered you already might be because that code is very badly structured and you might want to refactor it. If so, we can help you do that.

In the meantime, this bit of code might do want you want, assuming I have correctly analyzed what your code is doing.

'Insert this line at top of sub
    FooterAddress = "Xm:Yn"
    'Where, regarding the desired footer section on sheet 4:
    '   X is the column letter that "Order Location" is in
    '   n is the row number above above"Order Location"
    '   Y is the Column letter of the last column of the Comments section, and
    '   m is the row number below "Verified by"
Loop Until ws.Cells(sr, 1) = ""
    'insert the following after the line above
    With ThisWorkbook
        Sheet4.Range(FooterAddress).Copy Sheet1.Cells(dr + 1, 1)
    End With

[PureBluff]

Thanks SamT, the sheet we have is working quite well at the minute, we're just making tweaks to it as its now "Live"

I'll log in tomorrow and reply as I dont have the latest revision at home.

Thank you for your time so far.

Best regards

[PureBluff]

Sorry for the delayed response, I've been fairly busy this week.

I think if I summarise what we do in the operation, then it might help clarify what I need the spreadsheet to acheive or maybe it may just confuse it more?

We pull data from the same table as the pickers receive data to their guns, this accompanies it as a verification of tasks - They then wrap their pallets and send them direct to the customer, so they're fully accountble. The old working practice was removed with no phase in/out period - I took it upon myself to devise a spreadsheet that could be used for this function, hence why it's poorly structured/coded (this is my first ever project) --

Anyway, sorry to go off on a tangent.

We pull the data used in the curent order

Load Reference | Walk Sequence | Product Code | Location | Pallet # | QTY | Consignment | Destination

All fields are required for the final spreadsheet, with the exception of walk sequence, but we have to pull this to sort by. The data is varying in ize, from one line to 200+ lines.

I've built a basic template of a sheet, which has the Macro above recorded. It pulls data from the output file from the RDS task table (which isn't natively saved as .xls)

***Row 13 is now the first row of the variable pick task data***

As you can see, the loop is used to copy the data until the cell is blank, I copy over 6 blank cells too just so I can draw a boarder around them on import to create "check boxes" I then hide 3 columns which data is only required for the header of the document and simply remap the data using an =H13 forumla in the header....

Customers are in a large cell at the top, which has 2 VLOOKUP's played against it to pull customer specific information. We select the customer from a predefined list and it then pulls the required data via the VLOOKUP into the header.

I know it's long winded, but being very new to VBA, I didn't require a complex function, I needed something to work, and quick - this sheet was born (I can provide the templates and dummy data, if it makes things easier to understand)

Soin short, I'd love to structure the code better, but to be perfectly honest, I wouldn't know where to start,but I am willing to learn and can usually pick things up fairly fast. The current spreadsheet does work in practice now.

Also, FYI, your footer code worked fine to place it at the base of the document, sadly, it's cell width is (obviously) predefined byt the cells above it, is it possible to make it a bit more complex and have it merge a certain cell number wide?

Thank you again for your time and assistance, and thanks everyone else for looking.

Regards

Steven

[SamT]

Steven,

Worksheets are cheap. Put your footer on a new sheet all by itself. Format the sheets columns exactly like the sheet the footer gets pasted to, hidden and widths.

Do not use "Merge Cells. Use Webdings Font, lower case "c" for checkboxes.

Starting in cell "A1", design your footer as you like, include any empty border rows/columns all around.

Including "A1", select the entire footer with empty border cells. On the Excel Menu, Insert >> Name >> Define, Name the Selected Range "DocFooter." If you think you may ever want another footer, append a numeral to the name. Don't use "footer" as the name without 'pre-' or 'suf-' fixing something to it.

The code snippet above now becomes[VBA]Range("DocFooter").Copy Sheet1.Cells(dr + 1, 1) [/VBA]




Fortunately you have a working App "Live," so you can take some time to think about what you really need to do do make it a good one.

Structured Code Requires Structured Data.
Or; Someday You'll Want SQL.

Data Is For Code and Reports Are For People.
Or; Keep It Separate, Smarty.

Data is Forever, But Reports Are Kiss and Tell
Or; Intransient vs Transient.

Reports are forms and forms are reports. I use "Reports" for both except when talking about UserForms. for me, Form always means a (VBA) MS UserForm.

I'm going to refer to this Post: http://www.vbaexpress.com/forum/show...php?t=46847And this App:UserForm App.xls from that post.

The Post is to someone else who is designing their first App and the Download is an (incomplete) App I was making for someone. It contains in one book, a Report sheet, (transient data,) a Report Database, (permanent new data storage,) two value look up sheets, a Locations Sheet, (fixed data,) and a Data Entry Form definition sheet. The data structure and data retrieval code in it will be of interest to you.

Some Definitions for you:
Database = A group of Columns and Rows
Field Name = Column Label or column Range Name.
Field = A Column in a DB
Record = A Row in a DB
Record Field = One cell in a Record
Keys, primary and secondary = Unique values that identify unique records
Index = Key

From what you have said about your operation, it's a pretty good sized business. I would suggest that you consider putting Customer information in one DB with an Index number in Column A. The Index, or Primary Key is used to look up relevant records in other DBs. You can also have DB's for employees, product codes, locations, (by product code Index,) and Destinations. Any data that can be (semi-) permanently listed.

I realize that this post in Intense 'n Dense, so I'll check back in a few days.

[PureBluff]

Thanks SamT, I'll take a look at it over the weekend hopefully - as much as I'd love to only do it in my contractual hours - it works in the exact opposite way.

Have a good weekend.

**Edit** I've uploaded the current version of the document so you can see howw it functions for a better understanding of the data we use, and 'why'

h**ps://dropbox.com/s/v4itndm79ofotye/CHECKSHEETv2.95.xls & h**ps://dropbox.com/s/ye3scrcrz22m3n9/RDS%20Tasks%20by%20PLP%20with%20loc.xls

The second being the data that's queried from the database, it's pulled suing an application called Cognos Impromptu and natively saves as .IMR but can be saved as .xls or .csv - A fairly manual process but we don't have a license for Access to utilise OLE nor do I have the knowledge to use it.

[SamT]

Steven,

I just noticed this expensive error waiting to happen on the RDS Tasks sheee.

You have two identical records with different Qty-Requited values

PLP Walk-Seq Product-Code Location UPI QTY-REQ'D Consignment Pick-Lane
000164104 815 Y63739964 E116A 01798376 1 2535126 70A
000164104 815 Y63739964 E116A 01798376 20 2535126 70A

[PureBluff]

Thats just because im using old data in that report, it was a task split as the pick slot needed replenished. The picker would have 2 visits to that slot

[SamT]

Cognos is not a database, it is a GUI front end for people who don't want to learn SQL.

Some where in the company is a real DataBase.

VBA can talk to any database as yet created.

VBA can import CSV's with the click of a button.

Anyway, I'll think some more about what you shown me and get back to you later.