VBA To Create a Index Sheet of all VISIBLE sheets AND Populate with values from those sheets

[bhalbach]

I am trying to create an index sheet of all Visible sheets in a workbook, and also copy specific data from the sheets to the index sheet with VBA.

I have found some code that works great....Except it creates the Index sheet with ALL sheets in the workbook. I only want the sheets that are not hidden.

Also, while it is creating the Index sheet I would like it to copy data from each sheet to the Index sheet.

For Example if I have 20 visible sheets I would end up with a list of the sheet names on an index page...from these 20 sheets I would like to copy the data from cell's C5, I5 & J5 to the same row as the sheet name on the Index page.

The code I have now to make the index page is below, but it only copies ALL sheets to a Index sheet.

Please help...

Private Sub Worksheet_Activate()
'Updateby20150305
Dim xSheet As Worksheet
Dim xRow As Integer
Dim calcState As Long
Dim scrUpdateState As Long
Application.ScreenUpdating = False
xRow = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
For Each xSheet In Application.Worksheets
If xSheet.Name <> Me.Name Then
xRow = xRow + 1
With xSheet
.Range("A1").Name = "Start_" & xSheet.Index
.Hyperlinks.Add anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With

 

[bhalbach]

change this line:

.Range(Cells(2, 1), Cells(nsht + 1, 4)) = outarr

to

.Range(Cells(5, 4), Cells(nsht + 4, 7)) = outarr

How does this code line work so I can change where it pasts to whatever i want? I assume it is relative x,y of some sort but i can t make sense of it.

 

[offthelip]

When using vbas it is often very useful to be able to address a range using numbers rather than letter and numbers. this method of addressing a range is well worth learning
Just looking at the "cells" bit first, cells(2,1) refers to the cells which is in row 2 and the first column i.e A2. ( NOTE the order is ROW then COLUMN, the opposite of "A2", I have always thought this was a mistake on Microsfts part because they could have been the same) To refer to D5 where you want to start, it is obviously on Row 5 and we count A,B,C,D . ie 4 columns so the reference cells(5,4) refers to D5
The second "cells" is just refering to where the other end of the range is. We don't know what row it is we just know that it is the start row plus nsht, We are copying 4 columns so the end column number is going to be 3 plus what ever the start column is .
so this addressing mode is:
Range( Cells(startrow, startcolumn),cells(endrow, endcolumn))
so Range("D5:F12") is equal to
Range ( cells(5,4),cells(12,6))

 

[bhalbach]

change this line:

.Range(Cells(2, 1), Cells(nsht + 1, 4)) = outarr

to

.Range(Cells(5, 4), Cells(nsht + 4, 7)) = outarr

Sorry to keep asking for your help, I almost have it.

How does this code line work so I can change where it pasts to whatever i want? I assume it is relative x,y of some sort but i can t make sense of it.

Also, what if I only wanted the sheets starting at the 15th sheet to go to the index? Not the first 14 sheets included.

 

[bhalbach]

When using vbas it is often very useful to be able to address a range using numbers rather than letter and numbers. this method of addressing a range is well worth learning
Just looking at the "cells" bit first, cells(2,1) refers to the cells which is in row 2 and the first column i.e A2. ( NOTE the order is ROW then COLUMN, the opposite of "A2", I have always thought this was a mistake on Microsfts part because they could have been the same) To refer to D5 where you want to start, it is obviously on Row 5 and we count A,B,C,D . ie 4 columns so the reference cells(5,4) refers to D5
The second "cells" is just refering to where the other end of the range is. We don't know what row it is we just know that it is the start row plus nsht, We are copying 4 columns so the end column number is going to be 3 plus what ever the start column is .
so this addressing mode is:
Range( Cells(startrow, startcolumn),cells(endrow, endcolumn))
so Range("D5:F12") is equal to
Range ( cells(5,4),cells(12,6))

Also, what if I only wanted the sheets that get put on the Index sheet starting at the 15th sheet for example... Not the first 14 sheets included.

mom sure there would be a way to do that?