Just end the macro with Range("B2").Select assuming the first column/row are freeze paned.
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.
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.
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.
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?
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.
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;
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).Originally Posted by StephenR
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.
Last edited by peanut1999; April 23rd, 2008 at 07:43. Reason: Auto Merged Doublepost
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
The one with the fewest lines of code wins
There are currently 1 users browsing this thread. (0 members and 1 guests)