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

 

[eklaire89]

This seems more suitable as a query function. Are you able to easily use Access and create a query/ form or is the data populated by someone else in Excel?

 

[mumps]

Create a sheet named "Summary" and then run this macro:

Sub combineSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ws As Worksheet
    Dim rng As Range
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            ws.Range("A1:A" & LastRow).Copy Sheets("Summary").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            LastRow = Sheets("Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For Each rng In Sheets("Summary").Range("B2:B" & LastRow)
                If rng.Offset(0, -1) = "" Then
                    rng.Offset(0, -1) = ws.Name
                End If
            Next rng
        End If
    Next ws
    Sheets("Summary").Rows(1).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

 

[mistersteve]

Thanks Mumps,

it sort of worked.

in Summary sheet I get in column A "Sheet1" on all lines


did I do something wrong?

 

[mumps]

When I tested the macro on the data that you posted, it worked properly. If the organization of your actual data is slightly different, it may not work. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[mistersteve]

Hi Mumps,

I rebuilt a new wb, now nothing happens at all
see https://www.dropbox.com/s/7qxotw7xmboqsud/test.xlsm?dl=0

one other question, I would now like to expand the data collection to maybe 8 ws and expand each ws to 5 columns A-E

sorry to be a pain

 

[mumps]

I tried the file again with your original data and it worked properly. I don't know why it's not working for you. In regards to your other question, please upload a copy of your updated file. It would be easier to help if you upload your actual file rather than a sample. Macros may work on the sample data but not on actual data because the slightest difference in the data and how it is organized can make a huge difference.

 

[mistersteve]

Hi Mumps,

I tried on my main spreadsheet and opened up a new one.
same thing, it seems I have a runtime error91
As I don't understand VBA I am at a loss.
Can achieve the same results using a formula?

 

[mumps]

Can you upload a copy of your main spreadsheet?

 

[mistersteve]

He we go

https://www.dropbox.com/s/74puy3erwor7rc1/Stock Report.xlsm?dl=0