Addition of "2nd footer" for each page?

[thenapolitan]

Hey all,

Not sure how to really ask this question, but here goes:

I have a spreadsheet I'm working on that I need to add some sort of additional footer for each page to be placed above the existing built-in footer and span across the entire width of the page. I suppose it could be a text-box or something, but would prefer to have it be either be an additional row in the existing footer (not sure if this is possible) OR a merged cell across the bottom of the page, the same for each page.

That being said, if I had some code that could find the bottom row of each page, I might be able to work with making something on my own.

Thoughts?

Cheers,

Chris

 

[guexc1977]

It'd be pretty straight-forward to find the last row and if you know the last column, merge all the cells from first-to-last column and enter the text. I'm not sure that's necessary though. Plus, I don't think this way would put the text below your existing footer. Plus, do you need to automate this for multiple sheets?

If no, I think you'd be best off just going in the footer and pressing enter to go to the next row. Maybe put one footer in center, another in left.

If you really want to find the last row, the code (with "WS" as the worksheet code name) would be WS.UsedRange.Rows.Count. This returns the last "used" row which isn't fool proof but is a start.

 

[thenapolitan]

Plus, I don't think this way would put the text below your existing footer. Plus, do you need to automate this for multiple sheets?

Yeah, actually I would need to do this across multiple sheets, if multiple sheets exist. Also, if this makes it any easier, I was actually talking about making it ABOVE the existing footer.

If no, I think you'd be best off just going in the footer and pressing enter to go to the next row. Maybe put one footer in center, another in left.

This "extra" footer that I need actually needs to span the entire width of the sheet from column A over to the right-most column. That's why I was thinking a "merge" might work. That's another reason why I was thinking text box or "shape" because I can place it right over the sheet in the area I want if I can figure out where to place it. Problem with the text box idea is that I don't want to cover-up text that might be there.

If you really want to find the last row, the code (with "WS" as the worksheet code name) would be WS.UsedRange.Rows.Count. This returns the last "used" row which isn't fool proof but is a start.

I know about this code, BUT, this is just the last used row. Let's say my last used row is row 20, but the bottom of the page is row 35 - I'd need to get to that row 35. What I really need is to find the last row for each page after defining the Print Area page breaks.

Not sure if I'm making sense....haven't had enough coffee (or beer) yet today...

 

[guexc1977]

No, that makes sense. It appears you're comfortable finding the last row. Once you find it, here's some code to insert a page break which if done at your text added row +1 should work.

ActiveWindow.SelectedSheets.HPageBreaks.Add After:=Your text added row + 1