Solved

Combine multiple spreadsheets excel 2010

Posted on 2014-12-27
3
268 Views
Last Modified: 2014-12-27
Need to combine data from different sheets in different workbooks into one spreadsheet
0
Comment
Question by:lvsten10
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 500 total points
ID: 40520026
HI

is that what you are looking for?

In Excel, On the Data tab, in the Data Tools group, click Consolidate.

You can consolidate multiple sheets all together.

Here are more options available:
consolidate-data-from-multiple-worksheets
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40520046
Another way could be to consolidate all the sheets with an Excel Macro. You just need to adjust it for your needs.

But for me, i really like the Consolidate in the Data tab. It does the job very good.

Sub Consolidate_Excel()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Consolidate_sheet1"


    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")


    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A1").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select everything without the title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selectedin the new sheet
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub

Open in new window

0
 
LVL 10

Expert Comment

by:broro183
ID: 40520081
hi,

I have never used the Data - Consolidate function & I only have Excel 2007 so it may not be the same as in 2010.

However, in case you want a different approach to the ones that Wilder has suggested, do either of the below links help you?

Ron DeBruin has created an Excel Addin which may be of interest: RDB Merge

Brettdj's article

hth
Rob
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Online collaboration is quickly becoming embedded in the workplace, and its benefits are tangible. See what the current landscape looks like and what the future holds for collaboration tools and the future of work.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question