Typing name of sheets in cells

[tomas]

Hello

if I have sheets "list1""list2"and "list3". When I run macro I get correctly "list1" in cell B1 Then I should get "list2" in cell C1 but also cell B1 is overwritten to "list2". I thought current region should expand continuosly a select just another cell for input ......

Sub tets()

Dim singlesheet As Worksheet
Dim namesheet As String

Range("a1") = "sheet names"
For Each singlesheet In Worksheets
    namesheet = singlesheet.Name
    Range("a1").CurrentRegion.Offset(0, 1).Value = namesheet
   
Next
End Sub

 

[NARAYANK991]

Hi ,

Try this :

Sub test()
    Dim singlesheet As Worksheet
    Dim namesheet As String
    Dim i As Integer

    With ThisWorkbook
        Worksheets("Summary").Activate
        With ActiveSheet
              .Range("A1") = "sheet names"
              With .Range("A2")
                  i = 0
                  For Each singlesheet In Worksheets
                      namesheet = singlesheet.Name
                      .Offset(i).Value = namesheet
                      i = i + 1
                  Next
              End With
        End With
    End With
End Sub

Narayan

 

[tomas]

Thanks Narayan it worked for me