Problems freezing the header row form scrolling

Hello,

I've created a userForm to connect to an access database to copy data to a Worksheet. This works fine when the data is copied to "Sheet1".
When the data is copied to "Sheet2" an error is generated. "Select Method of Range class failed.
[VBA]
objWorksheet.Rows(2).Select
objExcel.ActiveWindow.FreezePane = True
[/VBA]

Even when the error is generated, the data is still copied to "Sheet2" but the header row scrolls.

I've attached a doc with the copy data routine only. The error is generated at line 71

Any help would be much appreciated.

Thanks

Karl

Make sure the sheet 2 is the active window. If sheet 1 already has frozen pane, it might generate that error.

Haven't check the file yet, but the first thing to check would be if Sheet2 is in fact the active window.

Hello,

Thanks for the reply.

How would I make Sheet2 the active window?

Regards

Karl

If the name of Sheet2 is "Sheet2" it would be:

[VBA]
Set objWorksheet = Sheets("Sheet2")
[/VBA]

Hello,

Once again thank you for your response. Unfortunately I'm still getting the error.

Thanks

Karl

[VBA]Worksheets(2).Activate
Worksheets(2).Rows(2).Select
ActiveWindow.FreezePanes = True[/VBA]
Are you doing this in excel ? Because you are creating an instance of excel ... in excel ???

Charlize

Hello Charlise

Thanks for the tip I was able to get it to work with objWorksheet.Activate

Regards

Karl