VBA to Copy Tab Name and Populate Column

[VBA learner ITG]

Thank you for looking at my post.

I was wondering if I could get your advice or guidance to a solution for my VBA issue.

I want to populate column A4 to A23 automatically on a Tab called "Saving Calculator" with the name of the tab or tabs that that the below VBA code creates.

Ideally if A4 was already named from a tab it would look at A5 and populate it with the new tab name and so forth until all blank cells in column A4 to A23.

Sub Copy_paste_tab()


Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("D5").Value <> "" Then
ActiveSheet.Name = wh.Range("D5").Value
End If
wh.Activate


End Sub

 

[VBA learner ITG]

Just to give you an update:

I have found the below VBA code which works but i was wondering how do i go about getting the data into the Tab called "savings Calculator" in column A4 downwards?

Sub Insert_Tab_Into_Cell()


Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i


End Sub

 

[VBA learner ITG]

Dear readers,

Instead of using VBA code I have decided to go with a formula which is dynamic to populate the tab names in my document.

I am using Mac (excel 365) so for me i had to go to:

Formulas - Define Name
Name for Data Range: Sheets
Select Range Cells: =TRANSPOSE(GET.WORKBOOK(1))&T(NOW())

In the workbook I entered the below formula into the cell and it is dynamic that will add and remove tabs created.

=IFERROR(INDEX(MID(sheets,FIND("]",sheets)+1,255),ROW(A1),1),"")


SORRY TO WASTE ANYONE TIME AND I HOPE THIS HELPS ANYONE IN THE SAME SITUATION AS ME!!!!!!!!!