How do i collate data from different sheets in one sheet?

[Sudhir Amin]

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 A22.

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).

 

[Billy Liddel]

Sudhir

Try this (summary on sheet1)

Sub SheetSummary()
r = 2
Worksheets(1).Select
Range("A1").Select

For i = 2 To Sheets.Count
Cells(r, 1) = Worksheets(i).Name
Range(Cells(r, 2), Cells(r, 5)).Value = Worksheets(i).Range("A22").Value
r = r + 1
Next
End Sub

Regards
Peter

 

[Sudhir Amin]

Hi Billy,

This is exactly how i wanted it and it worked just great. Thanks a lot buddy.

Regards
Sudhir

 

[Sudhir Amin]

Hi Billy,

Could you also please let me know that if there are filters set in a
particualar sheet, is there a way to run a macro so that there are no filters
anywhere in the file on any sheet??

Regards

 

[Don Guillett]

Sheets(i).AutoFilterMode = False
Sub SheetSummary()
application.goto sheets(1).Range("A1")
on error resume next
For i = 2 To Sheets.Count
Cells(i, 1) = sheets(i).Name
Sheets(i).AutoFilterMode = False
Range(Cells(i, 2), Cells(i, 5)).Value = sheets(i).Range("A22").Value
Next i
End Sub

 

[Sudhir Amin]

Hi,

This does not seem to be working and is giving me an error saying invalid
outside procedure. It goes into the view code and is highlighting the False
in the first line of the macro you have given me. Please let me know what i
am doing wrong.

Regards
Sudhir

 

[Don Guillett]

The "first" line was NOT part of the sub. It was just there to show you.
Delete it

 

[Billy Liddel]

Don

application.goto sheets(1).Range("A1")

Great I'll use this line from now on!

Regards
Peter

 

[Sowmya P]

hi,

Can you please tell me how will I pull out data from different workbooks to one work book?

I think sudhir's macro would help....