Excel Forum

Welcome to the Microsoft Excel Forum. Excel is one of the more popular topics here at Computer Learning Zone. This forum is for everyone to read but only students (paid customers) can post here. If you'd like to become a student, you can watch Excel Beginner Level 1 for free, and then purchase Level 2 for just $1.00 here. After you create a logon, you will have full access to the Forums.

Subscribe to this forum and receive an email update whenever new posts are added. You will also be notified by email if any conversations you are involved in receieve new comments.

Click Here to Post a comment and start a new discussion

Please read the FORUM RULES before posting.

Page 1 5 Upload Images Link
Bruno Segers
42 days ago

Hey guys,

Is there a formula or VBA code in Excel that can indicate in a cell how many pages of the so many pages in a specific sheet consists of?
Don't give me any comment to put it in de footer of header please. That i now how to do it. Read More...

Bruno Segers
41 days ago

This code does show the correct number of pages, but when printing, it stops its function on the following pages. Is there anyone who can help me further? I have attached the print preview and file.

Option Explicit

Sub Pagenumber()
    Dim xVPC As Integer
    Dim xHPC As Integer
    Dim xVPB As VPageBreak
    Dim xHPB As HPageBreak
    Dim xNumPage As Integer
    xHPC = 1
    xVPC = 1
    If ActiveSheet.PageSetup.Order = xlDownThenOver Then
        xHPC = ActiveSheet.HPageBreaks.Count + 1
    Else
        xVPC = ActiveSheet.VPageBreaks.Count + 1
    End If
    xNumPage = 1
    For Each xVPB In ActiveSheet.VPageBreaks
        If xVPB.Location.Column > ActiveCell.Column Then Exit For
        xNumPage = xNumPage + xHPC
    Next
    For Each xHPB In ActiveSheet.HPageBreaks
        If xHPB.Location.Row > ActiveCell.Row Then Exit For
        xNumPage = xNumPage + xVPC
    Next
    ActiveCell = "REV 1 - Strona " & xNumPage & " - " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub

Richard Rost
41 days ago

Hi Bruno. It's been a while since I've done anything like this with Excel VBA (Access is my forte). However, if memory serves you can calculate the number of printed pages in a Worksheet with:

Worksheets(i).PageSetup.Pages.Count

Also, please review the Forum Rules. I don't allow the posting of external links.

Set Focus with VBA Upload Images Link
Cecil Leforce
2 months ago

I need help with this code to set focus. I would like for it to set focus in column B when the code runs. Thanks

Sub New_Formatted_Row_With_Formula()
Dim rActive As Range

Set rActive = ActiveCell Read More...

Scott Axton
2 months ago

Range("B1").Select

Cecil Leforce
2 months ago

I would like for it to set focus in the first cell of the new row that it adds. Also where do i put the new code at in here?
Thanks

Show 6 More Replies...

Scott Axton
2 months ago

Sorry took me a bit to get back to this.  Try this:

Sub New_Formatted_Row_With_Formula()
  Dim rActive As Range
  Dim LastRowNumber As Long
  Dim NR As Long     'New Row

LastRowNumber = Cells(Rows.Count, 1).End(xlUp).Row
NR = Last_Row_Number + 1

Set rActive = Range("B" & NR)

ActiveSheet.Unprotect "9121381"
Application.ScreenUpdating = False

With Cells(Rows.Count, "B").End(xlUp)
    .EntireRow.Copy
    With .Offset(1, 0).EntireRow
        .PasteSpecial xlPasteFormats
        .PasteSpecial xlPasteFormulas
        .PasteSpecial xlPasteValidation

        On Error Resume Next

         .SpecialCells(xlCellTypeConstants).ClearContents
         .SpecialCells(xlCellTypeConstants).ClearNotes
        On Error GoTo 0
    End With
End With

rActive.Select

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    ActiveSheet.Protect "9121381"

End Sub

Scott Axton
2 months ago

Sorry - just noticed an error.
The line NR = Last_Row_Number + 1
Should be:

NR = LastRowNumber + 1

oops!

Cecil Leforce
2 months ago

Thanks for the help so for but it's still not doing what i would like for it to do. Can i send you the file? If you like you can call if that would help.
Thanks Cecil
423-912-1381

Richard Rost
2 months ago

Hi Cecil. Please make sure you have read the RULES for posting. It's not a problem if you ask someone else to help you, but don't expect them to call you for free support. I don't offer it, and I don't expect my students to either. Answering a few questions here and there in the Forums is one thing. A phone call or sending someone files is another.

Scott Axton
2 months ago

Cecil -
I'm glad to help as I can but right now I'm unable to take on more work. I'm just going into my "busy" season for both of my jobs.
PLUS I've pretty much exhausted my Excel VBA knowledge with what we've done. I'm mostly a db guy.
Feel free to expound on what it's doing (or not doing) and I'll help here if I can.

Alex Hedley
2 months ago

Have you tried the Macro Recorder?
Perform the actions you need and then see what code it produces then amend it.

Ctrl+Shift+Down +1 offset to get the next row

Forecasting Venue Upload Images Link
Walter Hamilton
2 months ago

I have an excel spreadsheet that lists all of our programs with the Period of Performance (PoP) Start, PoP End, Funded Amount, Invoiced Amount, and Funded Remaining. I'm trying to forecast monthly revenue over the remaining PoP. I'm currently using the following formula: Read More...