Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 72
Default macro to sum select columns in all workseets within a workbook

I need to write a macro to sum the same columns (ie.. G:O) in every worksheet
within a workbook. I need to add it to a macro that I already have which
splits sheet 1 into other sheets using the information in column A. I have
that macro working as well as using an auto fit macro for all sheets, but now
I need to total the columns in each sheet that was created. I have a macro
that works when it is one sheet but I don't know how to do it for all sheets.
--
Thank you, Jodie
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 257
Default macro to sum select columns in all workseets within a workbook

Hi, Jodie. You should repost this question in the Excel-programming forum;
this one is about Excel worksheet functions.

For anyone to be able to help you, you'll eventually need to post code
samples so someone (me, or whoever) can help you modify them. Or, if you
like, send me an email and I'll start walking you through it myself; happens
I still like teaching once in a while.

--- "Jodie" wrote:
I need to write a macro to sum the same columns (ie.. G:O) in every worksheet
within a workbook. I need to add it to a macro that I already have which
splits sheet 1 into other sheets using the information in column A. I have
that macro working as well as using an auto fit macro for all sheets, but now
I need to total the columns in each sheet that was created. I have a macro
that works when it is one sheet but I don't know how to do it for all sheets.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 72
Default macro to sum select columns in all workseets within a workbook

Thank you Bob. I would love for someone to teach me but I don't know if you
would want to. I hardly even know the basics when it comes to macros. Here
are the two items that Ihave been running independently, but I want to run at
once:

FIRST

Sub SaveEachSheetasFile()

Dim AWn As String, AWp As String, WS As Worksheet, WB As Workbook
Set WB = ActiveWorkbook
AWn = WB.Name
'This line sets the saveas path, currently set to active workbook's path
AWp = WB.Path

Application.ScreenUpdating = False
For Each WS In WB.Sheets


WS.Copy
ActiveWorkbook.SaveAs Filename:=AWp & "\" & WS.Name

'

ActiveWorkbook.Close
Next WS
' WB.Close 'Remove the comment from this line to close the original workbook
after macro is done
Application.ScreenUpdating = True
End Sub


Sub CreateSheets()

' Insert blank in Row 2
Rows("2:2").Select
Selection.Insert Shift:=xlDown

' Sort column A
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Declare our variables
Dim wks1 As Worksheet, wks2 As Worksheet
Set wks1 = Application.ActiveWorkbook.ActiveSheet

'Define a department that won't exist be found
CurrentDept = "NotAValidDept"

'Scroll through each row of the worksheet
For i = 1 To wks1.UsedRange.Rows.Count

'And get the department name for each row
thisDept = wks1.Cells(i, 2)

'If we are dealing with a new department...
If (Not thisDept = CurrentDept) Then

'make sure it has a name (not null)
If (thisDept = "") Then
thisDept = "NULL"

End If

'Create a new worksheet for the new department
CurrentDept = thisDept
Application.ActiveWorkbook.Sheets.Add.Move
after:=Worksheets(Worksheets.Count)
Set wks2 = Application.ActiveWorkbook.Sheets(Worksheets.Count )
wks2.Name = thisDept

'Copy the header row
wks1.Rows(1).Copy wks2.Rows(1)

End If

'Copy the data row
wks1.Rows(i).Copy wks2.Rows(wks2.UsedRange.Rows.Count + 1)

Next
End Sub
Sub DeleteBlankRow2()

' Deletes the Blank in row 2
Sheets("Sheet1").Select
Rows("2:2").Select
Selection.Delete Shift:=xlUp


MsgBox ActiveWorkbook.Path


End Sub

Sub NewSub()
Call CreateSheets
Call DeleteBlankRow2
Call SaveEachSheetasFile


End Sub

SECOND

Sub VBAMacro()


Dim rng As Range



' Autofit columns B through D

Columns("B:D").EntireColumn.AutoFit

' Adds columns totals for F through O

' Jodie to change how many rows between the last number and where
the total is
' Set rng = [G65536].End(xlUp).Offset("CHANGE THIS VALUE", 0)

Set rng = [G65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Set rng = [H65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Set rng = [I65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Set rng = [J65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Set rng = [K65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Set rng = [L65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Set rng = [M65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Set rng = [N65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Set rng = [O65536].End(xlUp).Offset(2, 0)
rng = WorksheetFunction.Sum(Range(rng.Offset(-1, 0), Cells(1,
rng.Column)))

Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").Select
Selection.EntireColumn.Hidden = True



Cells.Select
Range("D1").Activate
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.PrintGridlines = True
.Orientation = xlLandscape
.PrintTitleRows = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 5
End With
Rows("1:1").Select
Selection.Replace What:="SumOf", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWorkbook.Save
End Sub

Are you able to help with this?

--
Thank you, Jodie


"Bob Bridges" wrote:

Hi, Jodie. You should repost this question in the Excel-programming forum;
this one is about Excel worksheet functions.

For anyone to be able to help you, you'll eventually need to post code
samples so someone (me, or whoever) can help you modify them. Or, if you
like, send me an email and I'll start walking you through it myself; happens
I still like teaching once in a while.

--- "Jodie" wrote:
I need to write a macro to sum the same columns (ie.. G:O) in every worksheet
within a workbook. I need to add it to a macro that I already have which
splits sheet 1 into other sheets using the information in column A. I have
that macro working as well as using an auto fit macro for all sheets, but now
I need to total the columns in each sheet that was created. I have a macro
that works when it is one sheet but I don't know how to do it for all sheets.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select dynamic data in 2 columns, and repeat macro to next sheet Tasha Excel Discussion (Misc queries) 2 September 17th 09 07:00 PM
Macro won't select columns Vick Excel Discussion (Misc queries) 1 August 17th 07 02:17 AM
Select Number of Columns and Print Macro Don Excel Discussion (Misc queries) 0 November 10th 06 10:26 PM
Is it possible to build macro that compares diff workbook columns AMXAH Excel Worksheet Functions 2 December 9th 05 10:00 PM
select workbook macro Nigel Excel Discussion (Misc queries) 3 May 16th 05 01:45 PM


All times are GMT +1. The time now is 11:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"