Copying excel data from one sheet into another sheet

Question

I've been searching for this on google but no luck. I have multiple sheets in my excel workbook, and one sheet as a 'summary' page. I want the data from the other sheets to update in my summary page. Whenever someone adds a row or adds new data I would like that to flow to the summary page.

In other words, I have Sheet A, Sheet B, and Sheet C. I want all of their data to display on Sheet D and for Sheet D to update whenever Sheet A, Sheet B, or Sheet C changes.

Is this possible?

How the data from all the sheets should be orgenized in sheet D? — EBH, Jul 13 '16 at 20:03
Just needs to stack on eachother and display all of the content like in sheet A, B & C, just on top of one another — user3151956, Jul 13 '16 at 21:05

mtb · Answer 1 · 2016-07-14T06:19:48.027

0

UPDATE: This answer is based on a misunderstood question.

It is possible:

=SUM(B1:B8, Sheet1!B3:B8)

Where Sheet1 is another Sheet in the same excel file.

This is the simplest example for a SUM.

You can have a look at this tutorial.

edited Jul 14 '16 at 06:19

answered Jul 13 '16 at 17:00

mtb

1,350
16
32

Hi there, thanks for the response. Sorry, I should have been more clear. It isn't just numbers that are in the other data sheet, it is content as well. So we have a combination of numbers, text, and dates going across one row. We just want all of that data to display in Sheet D – user3151956 Jul 13 '16 at 17:05
How about this link: http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-do-i-mirror-worksheets-so-if-i-change-one/4a64f964-a9a7-4ca9-b7a2-df4765b92e36?auth=1? – mtb Jul 13 '16 at 17:25
Unfortunately that solution didn't work, I went through the thread and it looks like that works for separate excel sheets in different files. I'm looking for something that will literally take the contents of Sheet A, B, & C and display all of the contents on Sheet D, and reflect any updates if sheet A, B, & C are updated – user3151956 Jul 13 '16 at 21:04

score 0 · Answer 2 · answered Jul 13 '16 at 18:47

0

I wont be clean, but if you you can use this in SheetD A1 and drag it. =if(SheetA!A1="","",SheetA!A1)

If your cell is blank, it wont show anything. If it contains anything, it will show up.

Not sure if the sheets are 'competing' against each other, or if they display different things. In other words, do you have a value in SheetA A1 AND SheetB A1? If that's the case, how do you want it to react?

answered Jul 13 '16 at 18:47

Nickstoy

194
1
10

Hey there, I appreciate your comment. Unfortunately that didn't seem to do the trick. I tried that out, but what I'm in need of is something that will literally take all of the contents of Sheet A AND Sheet B AND Sheet C, and just display them on top of one another in Sheet D - So Sheet D literally has the contents of Sheet A, B, & C, Just stacked on eachother. And if we were to add another row in sheet a, it would be ideal if it added the row in sheet D. Hope that helps – user3151956 Jul 13 '16 at 21:02
yes, I have a solution for that. but before I post, what do you want to see on sheet D A1 if you have values in all sheets in the A1 position?? – Nickstoy Jul 13 '16 at 23:24
tell me what you want the result to be in the following example: https://snag.gy/JApZ1L.jpg – Nickstoy Jul 13 '16 at 23:31
I would like them to show one after the other. So if Sheet A has contents in A1 (and other columns/rows) I want that to be displayed first. After all of the contents of sheet A have been displayed, after that we can display Sheet B contents. I made an image to help explain a little better than I can :) thanks for all of your help! https://s32.postimg.org/5rmfk1ufp/Blank.png – user3151956 Jul 13 '16 at 23:32
Here you go, Nickstoy - https://s32.postimg.org/ety0e0pjp/JAp_Z1_L.jpg Essentially, just want all of the contents of sheet A, B, & C, to display one after the other, with a row seperator in between – user3151956 Jul 14 '16 at 15:37

score 0 · Answer 3 · answered Jul 14 '16 at 21:13

You can use this VBA code:

Sub copy_all()
'
' copy all sheets to D
'
    Dim rowCount As Integer

    Sheets("D").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete
    Sheets("A").Select
    Range("A2").Select
    Range(Selection, Selection.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("D").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("B").Select
    Range("A2").Select
    Range(Selection, Selection.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("D").Select
    rowCount = Cells(Rows.Count, 1).End(xlUp).Row 'column A has a value of 1
    Cells(rowCount + 1, 1).Select
    ActiveSheet.Paste
    Sheets("C").Select
    Range("A2").Select
    Range(Selection, Selection.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("D").Select
    rowCount = Cells(Rows.Count, 1).End(xlUp).Row 'column A has a value of 1
    Cells(rowCount + 1, 1).Select
    ActiveSheet.Paste
End Sub

Every time you run this macro, it's deleting all the content of sheet D, except the column headers, and copy sheets A, B and C one after the other to sheet D.

It has 2 basic assumptions, that can be changed easily:

The name of the sheets is A, B, C and D. I guess this is not true, so just replace all the lines like Sheets("A").Select by Sheets("NameOfSheet").Select.
The data in all sheets starts in cell A2. If not, simply replace all the lines like Range("A2").Select with the correct most upper left cell of the data.

One assumption cannot be changed easily - that the data on all sheets look just the same (same number of columns, at the same order).

Try this and tell me if it helped you ;)

Copying excel data from one sheet into another sheet

3 Answers3