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

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

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

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("A2:D2").Value
r = r + 1
Next
End Sub

Regards
Peter

"Sudhir Amin" wrote:

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

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

Hi Billy,

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

Regards
Sudhir
"Billy Liddel" wrote:

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("A2:D2").Value
r = r + 1
Next
End Sub

Regards
Peter

"Sudhir Amin" wrote:

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

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

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

"Billy Liddel" wrote:

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("A2:D2").Value
r = r + 1
Next
End Sub

Regards
Peter

"Sudhir Amin" wrote:

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

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

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("A2:D2").Value
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sudhir Amin" wrote in message
...
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

"Billy Liddel" wrote:

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("A2:D2").Value
r = r + 1
Next
End Sub

Regards
Peter

"Sudhir Amin" wrote:

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

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

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

"Sudhir Amin" wrote:

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sudhir Amin" wrote in message
...
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

"Sudhir Amin" wrote:

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

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

Don

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

Great I'll use this line from now on!

Regards
Peter

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sudhir Amin" wrote in message
...
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

"Sudhir Amin" wrote:

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

How do i collate data from different sheets in onesheet?
 

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