1

Need help with following code.

My workbooks can have many worksheets. Each worksheet has its own purpose. I like to create a new window for each worksheet and then tile them.

I took the following from support.microsoft and inserted my code:

  Sub WorksheetLoop2()

     ' Declare Current as a worksheet object variable.
     Dim Current As Worksheet

     ' Loop through all of the worksheets in the active workbook.
     For Each Current In Worksheets

        ' Insert your code here.
        Sheets.select
         ActiveWindow.NewWindow
         Windows.Arrange ArrangeStyle:=xlTiled
        ' This line displays the worksheet name in a message box.
        MsgBox Current.Name
     Next

  End Sub

It works but all new windows shows the first worksheet. I would like each window to show the next worksheet.

Also, I would like the new windows to start at second worksheet because I end up with an extra window at the end of the loop.

I cannot use sheet names because sheet names change ofen.

Your help is greatly appreciated.

6
  • What happens if you run this macro, then turn on the macro recorder and activate the sheets you need in each window. Close the macro recorder. That gives you a starting point. Replace sheet names with the sheet index to allow for sheet name changes.
    – teylyn
    Feb 3, 2017 at 22:06
  • 1
    I tried that then realized that the new macro will assume a fixed amount of sheets whereas there can be a different amount of sheets per book.
    – Bobo
    Feb 3, 2017 at 23:45
  • So, how do you solve that issue when you arrange the windows manually? Describe your logic and it can be put in code.
    – teylyn
    Feb 4, 2017 at 8:59
  • 1
    Each sheet in my workbook has either a reconciliation, comparison or historical trend. Sometimes I use charts. I can end up with 10 worksheets. I want to make a dashboard of all my sheets. When my supervisor reviews my work package, they can see all the sheets at once. After I have polished up the workbook, I have to activate a sheet, I click on view because the ribbon goes back to home each time, then click new window for each sheet. Then I go to arrange all tiled. Then I have to reverse activate the windows and hit arrange all again. It's time consuming.
    – Bobo
    Feb 4, 2017 at 18:20
  • 1
    I need each window to display the sheet that the code activated and tiled. That is all. I'll try another website.
    – Bobo
    Feb 6, 2017 at 18:12

1 Answer 1

0
Here's one way to iterate through the windows of the active workbook, set each to a particular sheet, and put a specific cell in the top left corner. 

Sub Arrange_windows()

'declarations
Dim a As Integer

    'loop through all windows of the active workbook
    'count backward so we end up with the windows arranged from
    '1ow to high after we arrange them at the end
    '"arrange" sequences windows from most recently active to least recently active
        For a = ActiveWorkbook.Windows.Count To 1 Step -1
        'For a = 1 To ActiveWorkbook.Windows.Count
            'activate window a
                ActiveWorkbook.Windows(ActiveWorkbook.Name & ":" & a).Activate
            'activate sheet a in the active window
                ActiveWorkbook.Sheets(a).Select

            'add logic here to decide what to do in the active window
            'one possibility is a "Select Case" statement:
            Select Case ActiveWindow.Index
                Case 1:
                    ActiveWorkbook.Sheets("Sheet1").Select
                    Application.Goto Reference:=Range("$A$1"), Scroll:=True
                Case 2:
                    ActiveWorkbook.Sheets("Sheet2").Select
                    Application.Goto Reference:=Range("$D$15"), Scroll:=True
                Case Else:
                    'do nothing
            End Select
        Next a

'arrange the windows sequentially from most
'recently active to least recently active
    ActiveWorkbook.Windows.Arrange (xlArrangeStyleTiled)

End Sub
2
  • Hi geodekl. I'm so happy that you found my question. The German lady in Australia was trying to get me to disclose personal information about myself. Plus she would be a b@#ch to work for. So, I bailed. I will try this code out today if I have time, or after next hump day. Lol.
    – Bobo
    Aug 18, 2017 at 18:22
  • Also, you have summarized my things in less than 22 words based on my posts. You are a genius.
    – Bobo
    Aug 18, 2017 at 18:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .