[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Combine multiple spreadsheets excel 2010

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

656 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