Every week i receive about 45-50 sheet. I have a macro which pulls all the
data from these sheets into a single excel file in different sheets and also
renames the sheets as per the name of the file. The macro also makes a
summary of the data in the sheets and sums it in the cells between A2:D2.

Is there a way where i can get the names of the sheets listed one below
another and also have the cells A2-D2 from each worksheet pasted in front of
the names of the worksheets in one single sheet (the summary sheet).