Macro to subtotal all sheets please help

[roberttkim]

Hey everyone,

I was wondering if someone knew how to code a macro that totals columns d through z on several worksheets that have various numbers of rows. Each sheet has the same number of columns but different numbers of rows. I could really use the help please.

 

[Yard]

Hi and welcome,

What do you want to end up with? A single figure which is the sum of all the values in D:Z of the relevant sheets?

If each sheet has a varying number of rows of data to be summed, what exists in the rows below that data (if anything), and is there a maximum number of rows to be included?

 

[roberttkim]

thank you for the reply. I just want a sum of each column underneath the columns starting with say column f and going all the way to the right. I need this for each sheet. The sheet range is created from getting extracting the details from a pivot table

 

[Yard]

Maybe

Sub Sums()
Dim ws As Worksheet
Dim rngCell, rngStart As Range
Dim strWorksheets As Variant
Dim i As Integer
strWorksheets = Array("Sheet1", "Sheet2", "Sheet3")
For i = 0 To UBound(strWorksheets)
    Set ws = ThisWorkbook.Worksheets(strWorksheets(i))
    Set rngStart = ws.Range("F1:Z1")
    For Each rngCell In rngStart
        ws.Cells(Rows.Count, rngCell.Column).End(xlUp).Offset(1, 0).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
    Next rngCell
Next i
End Sub

 

[roberttkim]

Wow. that works great. Thanks! you are the best. This will be a big help at work. Also can I just ask one more question? After this macro is run. i need to delete a few columns and edit the page format for all the sheets. I would like to have each sheet have the same page format as the sheet 1 so that they all print exactly in the same page format as sheet 1. I am trying to delete some columns on sheet 1 and set the page so it prints all the columns on one page and goes on to more pages as needed by the range. Is there a way to do this so that whatever page format changes i make on sheet 1 will take place also in sheet 2, 3, 4, etc?

Thanks so much! You are the best!

 

[Yard]

Difficult to "mimic" the column deletions on one sheet to another, so maybe we need to decide which way to determine the columns to be deleted:

i) are the columns to be deleted always the same and, if so, what are they?

ii) is it columns with certain column headers which are deleted and, if so, what are they?

 

[roberttkim]

Yard...thanks for the reply. You are definately the BEST! I don't need to delete. I just want all the sheets to have the same page format so that when it prints...it all looks the same. The sheets are details from a pivot table. However, I need the width of all the data to fit on one page but the length can be several. Help is much appreciated.

 

[Yard]

Not quite sure what your "page format" requirements are, but this will work on the active sheet for the used cell range and replicate all row heights and column widths from Sheet1. It then sets the print area to the same as Sheet1.

Is that any good?

Sub FixColRowFormats()
Dim rngAcross, rngDown As Range
Dim i, j As Integer
Dim wsMaster As Worksheet
 
Set wsMaster = Worksheets("Sheet1")
Set rngAcross = ActiveSheet.UsedRange.Rows(1)
Set rngDown = ActiveSheet.UsedRange.Columns(1)
 
For i = 1 To rngAcross.Columns.Count
    With rngAcross.Columns(i)
        .ColumnWidth = wsMaster.Columns(.Column).ColumnWidth
    End With
Next i
 
For j = 1 To rngDown.Rows.Count
    With rngDown.Rows(j)
        .RowHeight = wsMaster.Rows(.Row).RowHeight
    End With
Next j
 
ActiveSheet.PageSetup.PrintArea = wsMaster.PageSetup.PrintArea
 
End Sub

 

[hgufrin]

Is there a way to adapt the code below to all sheets regardless of Worsheet Name? My Sheet1 is called Accrual. Tomorrow it will be called Reversal..

The point is that the worksheet names change constantly.

 

[roberttkim]

Hey Yard,

Thanks for the reply again and again. I really appreciate it. However, this macro doesn't help me because I might have been confusing in my question. My problem is when i click details for my pivot table, the data sheets that result has data going from A->AP for each sheet and goes down various rows for each different sheet. Excel automatically put page breaks at say column J and then R. I want the page break to be at AP so that all the columns fit on one long legal size paper. The rows don't have to fit on one page. I was going to do it manually to sheet 1 by going into view--> page break preview--> and moving that blue vertical dotted line all the way to the right. Is there a way to duplicate the setting on sheet 1 for each sheet in the worksheet except the summary tab and the details tab?

I'm so sorry.