Results 1 to 8 of 8
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: inset name of the sheets into othaer sheet (2000 sr 1)

    Chip Pearson has a routine to get all the names which can be modified to list them an to even ignore particular ones.

    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inset name of the sheets into othaer sheet (2000 sr 1)

    Sub ListSheetNames()
    Dim lngRow As Long
    Dim wsh As Worksheet

    ' Column A must be text
    Worksheets("LISTA").Columns(1).NumberFormat = "@"

    lngRow = 2
    ' Loop through worksheets
    For Each wsh In ActiveWorkbook.Worksheets
    ' Test if worksheet name is a number
    If IsNumeric(wsh.Name) Then
    ' Put name in LISTA sheet
    lngRow = lngRow + 1
    Worksheets("LISTA").Range("A" & lngRow) = wsh.Name
    End If
    Next wsh

    ' Sort list of names.
    Worksheets("LISTA").Range("A3:A" & lngRow).Sort _
    Key1:=Worksheets("LISTA").Range("A3"), Header:=xlNo
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inset name of the sheets into othaer sheet (2000 sr 1)

    Kiss...(... and Patience;-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inset name of the sheets into othaer sheet (2000 sr 1)

    Sorry, I don't understand what you want to do. Could you try to explain more clearly?

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inset name of the sheets into othaer sheet (2000 sr 1)

    My "friend" (?)...

    Thsi si a piece of your great code, is possible to insert into LISTA sheet the name of sheet created with this piece of macro?

    Example the code assign sheet name 0000 put this name sheet into LISTA first cell A3, the piece of macro complete the cicle, andfter make a new sheet from var var_DIP, for example a sheet named 4500, put this name of sheet into A4 ecc...

    .............
    If str1 <> var_DIP Then
    For Each c In ActiveWorkbook.Sheets
    If c.Name = var_DIP Then
    Set Foglio1 = c: Exit For
    Else
    Set Foglio1 = Nothing
    End If
    Next c
    If Foglio1 Is Nothing Then
    Sheets("TEMPLATE").Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    Set Foglio1 = ActiveSheet
    Foglio1.Visible = xlSheetVisible
    Foglio1.Name = var_DIP
    ......

    Hope i am clear.

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inset name of the sheets into othaer sheet (2000 sr 1)

    See now...

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inset name of the sheets into othaer sheet (2000 sr 1)

    Try

    ...
    Foglio1.Visible = xlSheetVisible
    Foglio1.Name = var_DIP
    Worksheets("LISTA").Range("A65536").End(xlUp).Offs et(1, 0) = var_DIP
    ...

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    inset name of the sheets into othaer sheet (2000 sr 1)

    I have a wbook with many sheets named from 0000 to 9999 (for example 0000, 4300, 4500, 7200...ecc...).
    I would want to insert the name of each sheets into the column A of the sheet LISTA (start the copy of the name from A3)present into same wbook.
    Note: into wbook are present sheets TEST1, TEST2 and TEST3 for thsi sheets not is important to insert the name into sheet LISTA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •