Excel macro - copying formulas to different worksheets

[shinkley]

I have a workbook with several worksheets. On each worksheet, the last row has the sum total for the data. I would like to copy each 'sum row' to a worksheet called 'Totals'. This worksheet is already created.

I would like to first sheets totals to be in row1, the second sheet in row2, and so on...

If i do a copy-paste in Excel, the copying between sheets isn't a problem.

however, trying to accomplish this in a macro is where i am having troubles. The row i am copying from will have no relation to the row i am copying to.

How do i go about doing this?

 

[SkipVought]

shinkley,

2 comments

1) Why put totals at the bottom and NOT know where they are? Totals at the bottom is a vestage of paper, pencil & adding machine. Why force any interested party to search for the bottom of a table to get "the bottom line?"

Put you totals in ROW 1. Then YOU and everyone else will know EXACTLY where they are.

2) Is this something like Sheet1 is January and Sheet2 is February etc and the stuff on the sheets are SIMILAR data? This is a design mistake that many Excel usere make. Similar data should not be chopped up on separate sheets, 'cuz you run into this EXACT kind of problem -- hearding data from ALL these different places to a summary sheet. Chances are, if all your data were on ONE SHEET, the answer would be elementary, "my dear Watson"


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[shinkley]

Skip,

1) There are several sheets in this workbook. I would like to report the total for each sheet on a totals sheet, so the user doesn't have to go through each sheet to find the totals for each product.

2) The flat file imported from excel has exceeded the row capacity set by excel. I am now forced to break up the information to seperate sheets. Each sheet contains a product. Each group section of data is presented on a seperate sheet.

 

[SkipVought]

You ought to be using Access or some other means to store the data all together.

If you put your totals, for instance in A1 on each sheet, then the SUMMARY sheet can sum all the A1 cells like

=SUM(Sheet3:Sheet5!A1:A1)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TropicalFred]

You could try something like this:

Sub totsheets()
Dim wrksht, wrkshtname, rownum, colltr, totrownum

totrownum = 1

For Each wrksht In Worksheets

wrkshtname = wrksht.Name
Worksheets(wrkshtname).Activate
ActiveCell.SpecialCells(xlLastCell).Select
rownum = ActiveCell.Row
colltr = Chr(ActiveCell.Column + 97)
Range("A" & rownum & ":" & colltr & rownum).Select
Selection.Copy
Worksheets("Totals&quot.Activate
Range("A" & totrownum).Select
Selection.PasteSpecial xlValues
totrownum = totrownum + 1

Next

End Sub

Hope this helps.

-Fred