Saving an excel file so that all the sheets open with A1 in the top left corner when the file is opened

[HankJ]

Hello

I thought this would be a pretty simple process but I've spent hours trying to figure it out.

The file I am working on is a custom report with two sheets.

The first sheet has all the data, tidied up.

The second sheet is a copy of the first sheet, before it was tidied up and so has all the raw data, with some columns deleted.

On opening the file I've got it to open as I want by using.

IC50Cust.Select
IC50Cust.Range("A1").Select

Part of my difficulty is that when these two lines were one, IC50Custom.Range("A1").Select, I was always getting 1004's.

My second sheet, when I go to it, is blue in colour as if a Range is being highlighted.

I've been trying to not use "Select" but I can see no other way to get to where I want to be.

I hope this makes sense.

Thank you for your time.

Hj

 

[steve the fish]

Im not sure im entirely clear but by your title is it this?

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.Goto Sh.Range("A1"), True

End Sub

Put that in ThisWorkbook module.

 

[HankJ]

Im not sure im entirely clear but by your title is it this?

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.Goto Sh.Range("A1"), True

End Sub

Put that in ThisWorkbook module.

Hi Steve

Thank you for this. I'm not sure it will work as the saved file has to be an *.xlsx, so no code.

What I am needing is some code in my create custom file macro that will ensure that when the custom report is opened, each sheet is opened with the cell in A1 visible at the top left of the excel view.

Many thanks

Hj

 

[Fluff]

Whilst you can set A1 to be the active cell when you create the file, if somebody then opens the file & leaves (for example) X100 as the active cell, without code there is no-way of setting A1 to be the active cell again

 

[HankJ]

Whilst you can set A1 to be the active cell when you create the file, if somebody then opens the file & leaves (for example) X100 as the active cell, without code there is no-way of setting A1 to be the active cell again

Hello Fluff
thank you for your reply.
I want, when the customer opens the custom report, is that the first cell they sees on each sheet is always going to be A1 in the top left hand corner.

Often I find that after cutting and pasting from a data file to the custom report template file the last step on a particular sheet, say pasting a range into A12:X13 and then save file as, the final report name. When I open the report to check it, what I see is the range A12:X13 highlighted and center screen.

Many thanks for your time

Yours

Hj

 

[Fluff]

I want, when the customer opens the custom report, is that the first cell they sees on each sheet is always going to be A1 in the top left hand corner.

As far as I know there is now way of doing that without VBA.
You can set it that A1 will be visible when they first open the file, but if they save the file with X13 in the middle of the screen, then that is how it will be the next time they open the file.

 

[steve the fish]

You must be using code. You said in post 1 you were getting 1004 errors.