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

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
change this line:
VBA Code:
.Range(Cells(2, 1), Cells(nsht + 1, 4)) = outarr
to
VBA Code:
.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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,908
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
change this line:
VBA Code:
.Range(Cells(2, 1), Cells(nsht + 1, 4)) = outarr
to
VBA Code:
.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

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,136,795
Messages
5,677,783
Members
419,720
Latest member
kurman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top