?
Solved

Combine multiple spreadsheets excel 2010

Posted on 2014-12-27
3
Medium Priority
?
279 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 2000 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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