Macro modify

Question

Macro modify

Chaturvedi, Santosh 590

I want to change this macro so that the liste generted in Data File -- Cell F5 to end sall align in left

Sub CopyAndListSheets()

Dim wsData As Worksheet

Dim wsTemplate As Worksheet

Dim wsNew As Worksheet

Dim copyAfter As Worksheet

Dim cell As Range

Dim outRow As Long

Dim newName As String



Set wsData = ThisWorkbook.Worksheets("Data File")

Set wsTemplate = ThisWorkbook.Worksheets("Master Template_Collectives")

Set copyAfter = wsTemplate



outRow = 4



For Each cell In wsData.Range("C4:C15")

    If Trim(cell.Value) <> "" Then

        newName = CStr(cell.Value)

        

        wsTemplate.Copy After:=copyAfter

        Set wsNew = ActiveSheet

        wsNew.Name = newName

        Set copyAfter = wsNew

        

        wsData.Cells(outRow, "F").Value = newName

        wsData.Hyperlinks.Add _

            Anchor:=wsData.Cells(outRow, "F"), _

            Address:="", _

            SubAddress:="'" & newName & "'!A1", _

            TextToDisplay:=newName

        

        outRow = outRow + 1

    End If

Next cell

End Sub

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Aetherin 2,665 Independent Advisor

Hi @Chaturvedi, Santosh,

To generate the list starting from F5 and left-align its contents, change outRow to 5 and add the alignment setting after creating each hyperlink:

Sub CopyAndListSheets()

    Dim wsData As Worksheet
    Dim wsTemplate As Worksheet
    Dim wsNew As Worksheet
    Dim copyAfter As Worksheet
    Dim cell As Range
    Dim outRow As Long
    Dim newName As String
    Set wsData = ThisWorkbook.Worksheets("Data File")
    Set wsTemplate = ThisWorkbook.Worksheets("Master Template_Collectives")
    Set copyAfter = wsTemplate
    outRow = 5
    For Each cell In wsData.Range("C4:C15")
        If Trim(cell.Value) <> "" Then
            newName = CStr(cell.Value)
            wsTemplate.Copy After:=copyAfter
            Set wsNew = ActiveSheet
            wsNew.Name = newName
            Set copyAfter = wsNew
            wsData.Hyperlinks.Add _
                Anchor:=wsData.Cells(outRow, "F"), _
                Address:="", _
                SubAddress:="'" & newName & "'!A1", _
                TextToDisplay:=newName
            wsData.Cells(outRow, "F").HorizontalAlignment = xlLeft
            outRow = outRow + 1
        End If
    Next cell

End Sub

The list will now begin at F5, and each generated hyperlink in column F will be left-aligned.

I hope this information is helpful. If I have misunderstood any part of your question, or if the issue persists, please feel free to reply with additional details.

Aetherin 2,665 Reputation points Independent Advisor

2026-10-06T07:11:43.3666667+00:00

Hi @Chaturvedi, Santosh,

Have you had a chance to check the replies provided? Any update would be appreciated.
Chaturvedi, Santosh 590 Reputation points

2026-10-06T07:14:00.33+00:00

Hello Aetherin -- This formula is perfectly working.. Many thanks for the support provided. have nice day ahead !!
Aetherin 2,665 Reputation points Independent Advisor

2026-10-06T07:23:37.82+00:00

Hi @Chaturvedi, Santosh,

Thank you for confirming that it works.

If my answer helped resolve the issue, please kindly click "Yes" on my answer as well. This helps mark the solution as helpful and accepted, making it easier for other community members with the same issue to find the relevant information.

Thank you, and have a great day ahead!

Answer 2

Set the output cell’s horizontal alignment when writing each sheet name to column F. Since the list starts at F5, set outRow = 5 and apply xlLeft to each generated cell.

Sub CopyAndListSheets()

    Dim wsData As Worksheet
    Dim wsTemplate As Worksheet
    Dim wsNew As Worksheet
    Dim copyAfter As Worksheet
    Dim cell As Range
    Dim outRow As Long
    Dim newName As String
    
    Set wsData = ThisWorkbook.Worksheets("Data File")
    Set wsTemplate = ThisWorkbook.Worksheets("Master Template_Collectives")
    Set copyAfter = wsTemplate
    
    outRow = 5
    
    For Each cell In wsData.Range("C4:C15")
        If Trim(cell.Value) <> "" Then
            newName = CStr(cell.Value)
            
            wsTemplate.Copy After:=copyAfter
            Set wsNew = ActiveSheet
            wsNew.Name = newName
            Set copyAfter = wsNew
            
            wsData.Cells(outRow, "F").Value = newName
            wsData.Cells(outRow, "F").HorizontalAlignment = xlLeft
            
            wsData.Hyperlinks.Add _
                Anchor:=wsData.Cells(outRow, "F"), _
                Address:="", _
                SubAddress:="'" & newName & "'!A1", _
                TextToDisplay:=newName
            
            outRow = outRow + 1
        End If
    Next cell

End Sub

If the entire F5:F output area should always be left-aligned, this can also be set once before the loop:

wsData.Range("F5:F1000").HorizontalAlignment = xlLeft

That avoids setting alignment row by row.