Create a List based on Cell Values from multiple Sheets & Columns

[JimS]

I’m trying to create a list of every 5th Cell in Row 1 from every sheet whose name begins with “Sheet” starting with Cell B1 of each sheet. I would like to create the list on the sheet called “List”.

The data on the Sheets are in groups of 5 columns – that’s why I need every 5th cell. Column A has data titles, the data groupings start in Column B.

There can be several Sheets that begin with the name Sheet and each sheet’s UsedRange can vary in size.

Any ideas on what code that will copy every 5th cell (starting with B1) in Row 1 as long as there is data in the cells. I would also like to capture the Sheet Name and write that out to the "List" sheet. Column A would have the sheet Name (ie: Sheet1, Sheet2, Sheet3, etc) and Column B would have the Cell Value of cell B2.


Below is a routine that loops through only those sheets that name begin with the name Sheet and renames it and then write the new name out to the “List” Sheet.


Can the code in the ********* Box be modified to do the Copy/Paste of the cells needed (every 5th cell in the UsedRange)?

Thanks for any ideas…

JimS

[vba]
Sub ShtRename()

Dim sht As Worksheet
Dim xxx As Variant
Dim shChanges As Worksheet
Dim N As Long

Const strChanges As String = "List"

Application.ScreenUpdating = False

Set shChanges = Sheets(strChanges)
N = 2 'offset that will write sheets that were renamed on the report sheet

For Each sht In Worksheets
If sht.Name <> shChanges.Name Then
xxx = Empty

If Left(sht.Name, 5) = "Sheet" Then

*************************************************************
* On Error Resume Next *
* xxx = Left(Split(Split(sht.Range("A2").Value, "\\")(1), "\")(0), 25) *
* On Error GoTo 0 *
* If Not IsEmpty(xxx) Then sht.Name = xxx *
*************************************************************


' Write new names out to List sheet
shChanges.Cells(N, "A") = sht.Name
N = N + 1

End If
End If
Next sht

Application.ScreenUpdating = True

End Sub
[/vba]

[mdmackillop]

Can you post a sample workbook?

[JimS]

I have attached a file that has example data. I have manually created what I'm trying to create on the List sheet.