Collation of Sheet Names and Cell Details....

[backfromthebush]

Hi all,

I have a large Excel workbook with 400 or so sheets. Each sheet is titled by its product name. What I'm after is to be able to extract the title of each sheet (sheet name) AND the value in cell A3 of that sheet (which is the product code), and form a list from across the entire workbook.
Any ideas?

 

[Leith Ross]

Hello backfromthebush,

This example macro will combine the each sheet name and the value of "A3" into a string. However, it lacks code to anything with the string since you made no mention of what to do with it.

Sub Macro1()

  Dim strName As String
  Dim Wks As Worksheet

    For Each Wks In Worksheets
      strName = Wks.Name & Wks.Range("A3")
      'Add code here to do something with the string
    Next Wks

End Sub

 

[backfromthebush]

Thanks mate... that information probably would be helpful!

With the string i'd like it to be put into a new sheet (lets name it "Product Codes"), and simply have column A with the codes and column B with the sheet names.

 

[Leith Ross]

Hello backfromthebush,

This will either create the worksheet "Product Codes" or use it if it already exists. The product codes will be added into column "A".

Sub Macro1a()

  Dim ProdWks As Worksheet
  Dim R As Long
  Dim strName As String
  Dim Wks As Worksheet

   'Add the sheet if needed or use the existing one.
    On Error Resume Next
      Set ProdWks = Worksheets("Product Codes")
      If Err > 0 Then
         Set ProdWks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
         ActiveSheet.Name = "Product Codes"
         R = 1
      Else
         R = ProdWks.UsedRange.Rows.Count
      End If
    On Error GoTo 0
    
    
    For Each Wks In Worksheets
      If Wks.Name <> ProdWks.Name Then
         strName = Wks.Name & Wks.Range("A3")
         ProdWks.Range("A1").Offset(R, 0) = strName
         R = R + 1
      End If
    Next Wks

End Sub

Sincerely,
Leith Ross

 

[backfromthebush]

cheers.... works a treat!!