copying from multiple sheets to single sheet

[mistersteve]

Hello,

can anyone help please.

I have various sheets with similar layouts.
the data is updated every month.

	A
1	apple
2	pear
3	banana
4	grape

<colgroup><col width="24" style="width: 18pt; mso-width-source: userset; mso-width-alt: 877;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

Sheet 1

	A
1	banana
2	plum
3	cherry
4	orange
5	apricot

<colgroup><col width="24" style="width: 18pt; mso-width-source: userset; mso-width-alt: 877;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

Sheet 2

	A
1	apple
2	plum
3	grape
4	orange

<colgroup><col width="24" style="width: 18pt; mso-width-source: userset; mso-width-alt: 877;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

Sheet 3

I want to be able to export into one worksheet
with the sheet number they came from
and update every month.

see below.

	A	B
1	Sheet1	apple
2	Sheet1	pear
3	Sheet1	banana
4	Sheet1	grape
5	Sheet2	banana
6	Sheet2	plum
7	Sheet2	cherry
8	Sheet2	orange
9	Sheet2	apricot
10	Sheet3	apple
11	Sheet3	plum
12	Sheet3	grape
13	Sheet3	orange

<colgroup><col width="24" style="width: 18pt; mso-width-source: userset; mso-width-alt: 877;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

thank you for looking at this

 

[mumps]

You copied the macro into the worksheet code module for the "Summary" sheet. It should go into a regular module. In your original post, you indicated that you want the data in column A in each sheet to be copied and pasted into column B of the "Summary" sheet with the sheet name in column A of the "Summary" sheet. Could you please clarify in detail what you mean when you say:

I would now like to expand the data collection to maybe 8 ws and expand each ws to 5 columns A-E.

because this is different from what you described in your original post.