Retreiving last 6 Sheet names in different rows

[Firasath]

Hi,

I want to put previous 6 months' Sheet Names in last sheet of workbook. Sheet names are in MMM-YY format and each month new sheet would be added with new months data.

The requirement is to put each sheet names either in separate rows or in separate columns.

Can somebody please advise a way to accomplish so.

Regards.

 

[MARK858]

Assuming that your tabs are in order then the below will list the names from the previous 6 sheets in cell A2 down of the last sheet.

Sub GetShtName()
    Dim i As Long, x As Long
    x = 2
    For i = Worksheets.Count - 6 To Worksheets.Count - 1
        Worksheets(Worksheets.Count).Cells(x, 1) = Worksheets(i).Name
        x = x + 1
    Next
End Sub

 

[Firasath]

Assuming that your tabs are in order then the below will list the names from the previous 6 sheets in cell A2 down of the last sheet.

Sub GetShtName()
    Dim i As Long, x As Long
    x = 2
    For i = Worksheets.Count - 6 To Worksheets.Count - 1
        Worksheets(Worksheets.Count).Cells(x, 1) = Worksheets(i).Name
        x = x + 1
    Next
End Sub

Thanks Mark. It worked and pasted the results in last sheet. Is it possible to provide the specific sheet name and cell reference where the results are pasted.

 

[Firasath]

Or is it possible to create six functions for six previous sheets to get the sheet names in any cell.

 

[MARK858]

Explain exactly where you want the results to end up.

 

[Firasath]

Explain exactly where you want the results to end up.

Dear Mark,

Let me try to articulate clearly my requirement. I want to place this info in different locations in a sheet called "Summary". I have used six functions, one function for each sheet, to retrieve data from previous six sheets. For your reference, I am putting the code for that below. I want something similar to place the sheet names. With these functions, I can use it as formula and refer a cell to retrieve the data from previous sheets.

Or else can we edit the above code provided by you to specify the sheet name "Summary" to place the data instead of last sheet of the workbook.

Function PrevSheet6(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
        PrevSheet6 = Worksheets(xIndex - 6).Range(RCell.Address)
End Function

 

[MARK858]

I want to place this info in different locations in a sheet called "Summary"

But you haven't stated what the different locations are... I am going to work now and so will look at this tomorrow if no-one picks up on it.

P.S. if you wanted it in a sheet called "Summary" then you should have stated it in the first post rather than

I want to put previous 6 months' Sheet Names in last sheet of workbook.

 

[Firasath]

But you haven't stated what the different locations are... I am going to work now and so will look at this tomorrow if no-one picks up on it.

P.S. if you wanted it in a sheet called "Summary" then you should have stated it in the first post rather than

My apologies please as I was little bit confused related to the outcome of the results.