Copy total from diff sheets to one

Dell / 1525
March 16, 2009 at 16:30:09
Specs: Windows XP, 2gb
Hello, there are different sheets in a workbook and i have to create a summary sheet from those different sheets after automatically summing total in each each.

That total should come to summary sheet from each sheets..basically have to copy one total line from each sheet.

e.g. each sheet is named with different cities like "LONDON", "Tokyo" etc. showing data in fields like age, population, men, women.

so that data's total for different CITY should come in summary sheet.

Request you to help through MACRO/excel formula.

Warm Regards,
Ramesh


See More: Copy total from diff sheets to one

Report •


#1
March 17, 2009 at 08:08:02
Will this work?

On your Summary sheet:

Starting in B1 and going across, put a city in each cell of Row 1. (B1 = London, C1= Tokyo , etc.)

Starting in A2 and going down, put your catagories (A2 = Age, A3 = Population, etc.)

Now let's say the total for Age in London is in F23 on the London sheet. On your summary sheet, at the intersection of London and Age (B2) use this formula:

=London!F23

In B2, enter the = sign, then click on the tab for the London sheet, then click on F23, then click the green arrow or hit enter.


Report •

#2
March 17, 2009 at 08:18:39
Thanks for your response. this can be done for 4-5 number of sheets but I have more than 500 or 600 (it depends month to month) sheets so I want two things first in each sheet a Macro should total automatically and then pull that total in Summary sheet.

Regards,



Report •

#3
March 17, 2009 at 10:34:45
First, what works for 4 sheets, works for 400, it just takes longer. ;)

Second, it may make sense to use a macro to set up the summary sheet (maybe by creating the column headings based on the sheet names, or something similiar) and then use formulae to keep the summary sheet up to date. It's hard to know the best approach without more details.

Finally, without knowing the layout of your workbook, it wouldn't be possible for us to create a macro that you could just drop into your workbook and run. The code would need to know where the data is and where you want it to go.

From a generic standpoint, this code will copy Row 23 from each sheet and paste it into the next empty row in a summary sheet. Maybe this will get you started.

For simplicity's sake, let's assume your summary sheet is Sheet1 and is named Summary.

Sub CopyTotals()
'Loop through sheets, starting at Sheet2
  For Sht = 2 To Sheets.Count
'Increment Row Number
   NxtRow = NxtRow + 1
'Copy & Paste
   Sheets(Sht).Rows(23).Copy Destination:=Sheets("Summary").Range("A" & NxtRow)
  Next
End Sub


Report •

Related Solutions

#4
March 17, 2009 at 11:43:14
Hi,
Many thanks for the response- just adding sheet layouts..

That's true -Summary sheet column headings are based on the sheet names.

& Summary sheet row fields are same as sheets.

and Total for sheets should be at the last row of data in sheets (or row number 1000 which ever seems convinient to you).

LAYOUT of SUMMARY sheet:
Name of city age men women
london
tokyo
new york
123city

LAYOUT for sheets : first sheet name "london
Name of city age men women
london 12 45 890
london 78 600 90

Total (to be done automatically )- to be pulled in summary sheet

layout for sheets : 2nd sheet name "new york"
Name of city age men women
new york 12 45 890
new york 78 600 90

Total (to be done automatically )- to be pulled in summary sheet

Best Regards,


Report •

#5
March 17, 2009 at 12:27:09
Explain this:

re: Summary sheet column headings are based on the sheet names

LAYOUT of SUMMARY sheet:
Name of city age men women
london
tokyo
new york
123city

You say the Sheets are named for the cities and you say the Summary sheet's column headings match the sheet names, yet you list the cities in the first column of the Summary sheet.

What am I missing?


Report •

#6
March 17, 2009 at 13:36:30
I'm sorry for the confusion, sheet names are basically FIRST COLUMN of the summary sheet like London, New York, Tokyo, etc.

Regards,


Report •


Ask Question