VBA - Enter worksheet names into a "Master Sheet" and copy data

[jonny1984]

Hi there

I am trying to do something probably quite simple but read a few tutorials and been unable to figure it out..... so calling for help !!

I know I cant add attachments so have saved an Excel file into dropbox - link below

https://www.dropbox.com/s/jg8nwxr5s5tpaj1/World datas.xlsx?dl=0


I am looking for a macro / VBA code that when run will automatically populate the "Master sheet" with

1. The names of the workbooks contained within the file - this will change as we might delete some old workbooks and then add new workbooks in - would be great to have a button or a macro we can run to re populate essentially a "chapter list" of the workbooks

2. Then copy the data in each workbook which can be of varying number of rows.. from 5 to 100 say (but would always start in cell A1) into the Master sheet as well so it could easily be compared and charts/graphs drawn etc

I really hope that's clear .. the file uploaded is "fully populated" .. basically looking for a macro that if I delete all the cells in the "Master" sheet, would re populate them as they are currently !


Thank you in advance so much for your help !!

Jonny

 

[footoo]

In your post, all instances of "Workbooks" should read "Worksheets"

varying number of rows.. from 5 to 100 say (but would always start in cell A1)

The sample data is not like this. The data on all sheets are in column B only, starting at B6.
Please clarify what you need.

 

[jonny1984]

In your post, all instances of "Workbooks" should read "Worksheets"


The sample data is not like this. The data on all sheets are in column B only, starting at B6.
Please clarify what you need.

Hi footoo

Sorry! I am not an Excel expert (as is probably painfully clear)

You are right it should be Worksheets is what I meant (I actually knew that just tired and writing late at night!)

You are also right the data in the sheet is in column -but it should be in column A .. it will be in column A when I have the actual sheet with working data


Thanks!
Jonny

 

[footoo]

I have assumed that row 1 in the source sheets contains headers.
Also, your Master sheet doesn't make sense to me, so I have omitted the second column (the one with the sheet names).
See if this does what you want :

Sub Summary()
Dim col%, rw%, ws As Worksheet
col = 1
rw = 1
Application.ScreenUpdating = False
With Sheets("Master")
    .Cells.ClearContents
    For Each ws In Worksheets
        If Not ws.Name = "Master" Then
            col = col + 1
            rw = rw + 1
            ws.Range(ws.[A2], ws.Cells(Rows.Count, "A").End(xlUp)) _
                .Copy .Cells(2, col)
            .Cells(1, col) = ws.Name
            .Cells(rw, 1) = rw - 1
        End If
    Next
    .Cells.EntireColumn.AutoFit
End With
End Sub

 

[footoo]

Also, if the second column of the Master sheet is omitted, no point in having sequential numbers in column A :

Sub Summary()
Dim col%, ws As Worksheet
col = 1
Application.ScreenUpdating = False
With Sheets("Master")
    .Cells.ClearContents
    For Each ws In Worksheets
        If Not ws.Name = "Master" Then
            col = col + 1
            ws.Range(ws.[A2], ws.Cells(Rows.Count, "A").End(xlUp)) _
                .Copy .Cells(2, col)
            .Cells(1, col) = ws.Name
        End If
    Next
    .Cells.EntireColumn.AutoFit
End With
End Sub