Excel VBA - Get parent window of worksheet

Question

I have a Sub that inserts a header from a template and freezes the top row of the active worksheet, which is written as,

Sub HeaderInsert(headerTemplate As Worksheet)
    headerTemplate.Rows("1:1").Copy
    ActiveSheet.Rows("1:1").Select
    ActiveSheet.Paste
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With
End Sub

I want to turn it into a function which is passed the sheet to insert the header into. So that it would be written,

Function HeaderInsert(headerTemplate As Worksheet, contentSheet as Worksheet)

ActiveSheet becomes contentSheet, but how can I get the Window of contentSheet?

Also is a better way to do that copy and paste?

score 14 · Accepted Answer · edited May 23 '17 at 12:07

14

I think you want contentSheet.Parent.Windows(1), e.g.:

Sub test()
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook

Set ws = ActiveSheet
Set wb = ws.Parent
Debug.Print wb.Windows(1).Caption
End Sub

As for the better way to paste: headerTemplate.Rows("1:1").Copy ActiveSheet.Rows("1:1")

More generally, you want to avoid Select unless necessary.

edited May 23 '17 at 12:07

Community

1
1

answered Dec 19 '13 at 00:50

Doug Glancy

27,214
6
67
115

Excel VBA - Get parent window of worksheet

1 Answers1