copying from multiple sheets to single sheet

mistersteve

Board Regular
Joined
Aug 18, 2014
Messages
110
Office Version
  1. 365
Hello,

can anyone help please.

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


A
1apple
2pear
3banana
4grape
<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
1banana
2plum
3cherry
4orange
5apricot
<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
1apple
2plum
3grape
4orange
<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.

AB
1Sheet1apple
2Sheet1pear
3Sheet1banana
4Sheet1grape
5Sheet2banana
6Sheet2plum
7Sheet2cherry
8Sheet2orange
9Sheet2apricot
10Sheet3apple
11Sheet3plum
12Sheet3grape
13Sheet3orange
<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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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?
 
Upvote 0
Create a sheet named "Summary" and then run this macro:
Code:
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
 
Upvote 0
Thanks Mumps,

it sort of worked.

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


did I do something wrong?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Can you upload a copy of your main spreadsheet?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top