Copy information from all sheets and store on final sheet.
 

I have gotten so much help from this site, and I thank all those you have
assisted me with my project. Anyway, I am down to the final stages of the
project and I am stuck with one last report to generate.

I have a workbook with a dynamic number of sheets, each sheet individually
named for an employee and holding there performance stats; these sheets are
generated and named from a different module.

On each employee named sheet there is a range of cells (C2:M2) I need to
copy and paste to an admin sheet. Below I have shown an example of how I need
the output to look.

Stephen King 296.00 6:59:31 55:27:42 29:49:01 31.00 119:27:30 0:03:19 194.00
30.99
(Sheet name) ( Stats copied from range (C2:M2) on each
sheet )

Now, if the sheets were static, each keeping the same underlying sheet name
(sheet1, sheet2, sheet3, ect) it would not be an issue, but since each time
the workbook is opened and used all the sheets are deleted and recreated,
this information is also dynamic.

I need to pull this information from all but two sheets in the workbook;
those sheets are named €˜Admin Sheet and €˜Raw Data

If anyone can assist, I would greatly appreciate it.

Thank you in advance.

Mahnian

Copy information from all sheets and store on final sheet.
 

for each sh in worksheets
if lcase(sh.name) < "admin sheet" and _
lcase(sh.name) < "raw data" then


end if
Next

--
Regards,
Tom Ogilvy


"Mahnian" wrote:

I have gotten so much help from this site, and I thank all those you have
assisted me with my project. Anyway, I am down to the final stages of the
project and I am stuck with one last report to generate.

I have a workbook with a dynamic number of sheets, each sheet individually
named for an employee and holding there performance stats; these sheets are
generated and named from a different module.

On each employee named sheet there is a range of cells (C2:M2) I need to
copy and paste to an admin sheet. Below I have shown an example of how I need
the output to look.

Stephen King 296.00 6:59:31 55:27:42 29:49:01 31.00 119:27:30 0:03:19 194.00
30.99
(Sheet name) ( Stats copied from range (C2:M2) on each
sheet )

Now, if the sheets were static, each keeping the same underlying sheet name
(sheet1, sheet2, sheet3, ect) it would not be an issue, but since each time
the workbook is opened and used all the sheets are deleted and recreated,
this information is also dynamic.

I need to pull this information from all but two sheets in the workbook;
those sheets are named €˜Admin Sheet and €˜Raw Data

If anyone can assist, I would greatly appreciate it.

Thank you in advance.

Mahnian

Copy information from all sheets and store on final sheet.
 

That worked wonderfully, now if anyone knows how to do the second part..
Where I pull that single range of data from every otehr sheet and add them in
a list to the admin sheet..

rep1
rep2
rep3

and so on.

"Tom Ogilvy" wrote:

for each sh in worksheets
if lcase(sh.name) < "admin sheet" and _
lcase(sh.name) < "raw data" then


end if
Next

--
Regards,
Tom Ogilvy


"Mahnian" wrote:

I have gotten so much help from this site, and I thank all those you have
assisted me with my project. Anyway, I am down to the final stages of the
project and I am stuck with one last report to generate.

I have a workbook with a dynamic number of sheets, each sheet individually
named for an employee and holding there performance stats; these sheets are
generated and named from a different module.

On each employee named sheet there is a range of cells (C2:M2) I need to
copy and paste to an admin sheet. Below I have shown an example of how I need
the output to look.

Stephen King 296.00 6:59:31 55:27:42 29:49:01 31.00 119:27:30 0:03:19 194.00
30.99
(Sheet name) ( Stats copied from range (C2:M2) on each
sheet )

Now, if the sheets were static, each keeping the same underlying sheet name
(sheet1, sheet2, sheet3, ect) it would not be an issue, but since each time
the workbook is opened and used all the sheets are deleted and recreated,
this information is also dynamic.

I need to pull this information from all but two sheets in the workbook;
those sheets are named €˜Admin Sheet and €˜Raw Data

If anyone can assist, I would greatly appreciate it.

Thank you in advance.

Mahnian

Copy information from all sheets and store on final sheet.
 

Sub copyData()
Dim sh1 as worksheet, sh as worksheet
Dim rng as Range, rw as Long
set sh1 = Worksheets("Admin Sheet")
rw = 3
for each sh in worksheets
if lcase(sh.name) < "admin sheet" and _
lcase(sh.name) < "raw data" then
set rng = sh.Range("C2:M2")
sh1.Cells(rw,1) = sh.name
rng.copy sh.Cells(rw,3)
rw = rw + 1
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"Mahnian" wrote:

That worked wonderfully, now if anyone knows how to do the second part..
Where I pull that single range of data from every otehr sheet and add them in
a list to the admin sheet..

rep1
rep2
rep3

and so on.

"Tom Ogilvy" wrote:

for each sh in worksheets
if lcase(sh.name) < "admin sheet" and _
lcase(sh.name) < "raw data" then


end if
Next

--
Regards,
Tom Ogilvy


"Mahnian" wrote:

I have gotten so much help from this site, and I thank all those you have
assisted me with my project. Anyway, I am down to the final stages of the
project and I am stuck with one last report to generate.

I have a workbook with a dynamic number of sheets, each sheet individually
named for an employee and holding there performance stats; these sheets are
generated and named from a different module.

On each employee named sheet there is a range of cells (C2:M2) I need to
copy and paste to an admin sheet. Below I have shown an example of how I need
the output to look.

Stephen King 296.00 6:59:31 55:27:42 29:49:01 31.00 119:27:30 0:03:19 194.00
30.99
(Sheet name) ( Stats copied from range (C2:M2) on each
sheet )

Now, if the sheets were static, each keeping the same underlying sheet name
(sheet1, sheet2, sheet3, ect) it would not be an issue, but since each time
the workbook is opened and used all the sheets are deleted and recreated,
this information is also dynamic.

I need to pull this information from all but two sheets in the workbook;
those sheets are named €˜Admin Sheet and €˜Raw Data

If anyone can assist, I would greatly appreciate it.

Thank you in advance.

Mahnian

Copy information from all sheets and store on final sheet.
 

Thank you for this, though it does nto seem to work quite right.

It will copy the names from each sheet as I need it, but it will not copy
the information from the individual sheets.

I have looked over it, with my limited knowledge, and can not see anythign
that is stopping this.

Thank you so much.

"Tom Ogilvy" wrote:

Sub copyData()
Dim sh1 as worksheet, sh as worksheet
Dim rng as Range, rw as Long
set sh1 = Worksheets("Admin Sheet")
rw = 3
for each sh in worksheets
if lcase(sh.name) < "admin sheet" and _
lcase(sh.name) < "raw data" then
set rng = sh.Range("C2:M2")
sh1.Cells(rw,1) = sh.name
rng.copy sh.Cells(rw,3)
rw = rw + 1
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"Mahnian" wrote:

That worked wonderfully, now if anyone knows how to do the second part..
Where I pull that single range of data from every otehr sheet and add them in
a list to the admin sheet..

rep1
rep2
rep3

and so on.

"Tom Ogilvy" wrote:

for each sh in worksheets
if lcase(sh.name) < "admin sheet" and _
lcase(sh.name) < "raw data" then


end if
Next

--
Regards,
Tom Ogilvy


"Mahnian" wrote:

I have gotten so much help from this site, and I thank all those you have
assisted me with my project. Anyway, I am down to the final stages of the
project and I am stuck with one last report to generate.

I have a workbook with a dynamic number of sheets, each sheet individually
named for an employee and holding there performance stats; these sheets are
generated and named from a different module.

On each employee named sheet there is a range of cells (C2:M2) I need to
copy and paste to an admin sheet. Below I have shown an example of how I need
the output to look.

Stephen King 296.00 6:59:31 55:27:42 29:49:01 31.00 119:27:30 0:03:19 194.00
30.99
(Sheet name) ( Stats copied from range (C2:M2) on each
sheet )

Now, if the sheets were static, each keeping the same underlying sheet name
(sheet1, sheet2, sheet3, ect) it would not be an issue, but since each time
the workbook is opened and used all the sheets are deleted and recreated,
this information is also dynamic.

I need to pull this information from all but two sheets in the workbook;
those sheets are named €˜Admin Sheet and €˜Raw Data

If anyone can assist, I would greatly appreciate it.

Thank you in advance.

Mahnian