Announcement

Collapse
No announcement yet.

Show/Position A1 To Top left Of Screen

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Show/Position A1 To Top left Of Screen

    I have a very large workbook with multiple sheets and use macros to copy and paste all data as values, then delete some rows and columns depending on certain criteria. This starts at cell A1 and works right, then down.

    This is then saved as a copy and distributed to a wide audience.

    The problem is that when each page is opened up, the data shown is the bottom right of the whole sheet instead of the top left (Panes are frozen for row and column headers).

    I'm not sure I've explained properly, apologies if not. Please go easy on me this is my first post.

  • #2
    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Just end the macro with Range("B2").Select assuming the first column/row are freeze paned.

    Comment


    • #3
      Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

      Depending on how your sheets are referenced in the macro, perhaps like this?
      Code:
      Application.Goto ActiveSheet.Range("A1")

      Comment


      • #4
        Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

        Sorry, I realise I haven't explained properly. There are over 60 sheets and the panes are not frozen in the same place on each sheet. I tried Range("a1").Select at the end of the macro but the view is still not right, even though it does go to that cell.

        Comment


        • #5
          Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

          Using either of our suggestions will take you to the upper left corner. Are you now saying that you want a particular range to be visible? Btw if the freeze panes don't follow any logical pattern I don't think you can write appropriate code.

          Comment


          • #6
            Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

            Yes I want the visible range to be A1 to whatever fits in the window without hiding anything behind the panes. The panes are frozen to enable the end users to scroll right and down without losing the row/column headers.

            Does that make sense?

            Comment


            • #7
              Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

              Sorry but no! What exactly did my code not do that you want doing because to my eyes it fits the bill?

              Comment


              • #8
                Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

                Site seems to be on a go-slow...

                I do understand what you mean now. The only way I can think of off the top would be store the cell at which you freeze panes and then reference that cell in the Goto statement.

                Comment


                • #9
                  Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

                  This code doesn't do what I want because columns G:AJ are hidden and rows 5:120 are hidden where panes are frozen at G5 and last cell containing data is AT155.

                  Essentially I need the code equivalent of Ctrl Home, if you see what I mean.
                  Auto Merged Post Until 24 Hrs Passes;

                  Originally posted by StephenR
                  Site seems to be on a go-slow...

                  I do understand what you mean now. The only way I can think of off the top would be store the cell at which you freeze panes and then reference that cell in the Goto statement.
                  I think I know what you mean however the panes are frozen in a different place on the worksheets (already frozen - not part of macro).

                  If there is no work-around I will just have to carry on manually doing Ctrl Home on each page before I send it out, a bit of a pain as it's a daily report. Never mind, nothing ventured nothing gained.

                  Many thanks for your suggestions.

                  Paula
                  Last edited by peanut1999; April 23rd, 2008, 06:43. Reason: Auto Merged Doublepost

                  Comment


                  • #10
                    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

                    Not sure if this helps. Try the ScrollColumn, ScrollRow command to get the uppermost A1 cell to always display. It may not be displaying for you using the Select command because you have ScreenUpdating = False.
                    Code:
                    ActiveWindow.ScrollColumn = 1
                        ActiveWindow.ScrollRow = 1
                        Range("B4").Select
                        ActiveWindow.FreezePanes = True
                    Bert
                    The one with the fewest lines of code wins

                    Comment


                    • #11
                      Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

                      Originally posted by Bert01
                      Not sure if this helps. Try the ScrollColumn, ScrollRow command to get the uppermost A1 cell to always display. It may not be displaying for you using the Select command because you have ScreenUpdating = False.
                      Code:
                      ActiveWindow.ScrollColumn = 1
                          ActiveWindow.ScrollRow = 1
                          Range("B4").Select
                          ActiveWindow.FreezePanes = True
                      Thanks but this didn't work for me. However it got me thinking about scrolling and I finally came up with the following:

                      Code:
                      Private Sub Worksheet_Activate()
                      Range("a1").Select
                      ActiveWindow.LargeScroll up:=5
                      ActiveWindow.LargeScroll toleft:=5
                      End Sub
                      ...which works perfectly and I've applied it to the code for each sheet so that it re-sets the page every time. (Note: I have set it to scroll 5 pages left and 5 pages up which covers the biggest worksheet and doesn't error on my smallest sheet.)

                      Many thanks for all your help.

                      Comment

                      Working...
                      X