Formatting within this macro

I am new to VBA and can follow along fairly well, but don't know how to do 
certain things.  I have the following macro that runs like a champ, but now I 
need to automatically format the SummarySheet.  Specifically, I need to set 
Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in 
Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1 
and 3 to a height of 6 and have a color of Light Gray.  Then I need to change 
the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the 
bottom of Column F that says "Totals" and then sum all the values in Column H 
from H4 to the LastRow.  Any help would be greatly appreciated.

Sub Summary_All_Worksheets_With_Formulas()
    Dim Sh As Worksheet
    Dim Newsh As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

   'Delete the sheet "SummarySheet" if it exists
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("SummarySheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "SummarySheet"
    Set Basebook = ThisWorkbook
    Set Newsh = Basebook.Worksheets.Add
    Newsh.Name = "SummarySheet"
    
    'Add headers on row 2
    Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
"", "Profitability", "", "Residuals")

    'The links to the first sheet will start in row 4
    RwNum = 3

    For Each Sh In Basebook.Worksheets
        If Sh.Name <> Newsh.Name And Sh.Visible Then
            ColNum = 2
            RwNum = RwNum + 1
            'Create a link to the sheet in the B column
            Newsh.Cells(RwNum, 2).Formula = 
"=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
            & """" & Sh.Name & """)"
            For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
                ColNum = ColNum + 2
                Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
myCell.Address(False, False)
            Next myCell

        End If
    Next Sh

    Newsh.UsedRange.Columns.AutoFit

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = True
    End With
End Sub
-- 
Nothing in life is ever easy - just get used to that fact.
0
Utf
1/29/2010 3:04:01 PM
excel.programming 6508 articles. 2 followers. Follow

12 Replies
949 Views

Similar Articles

[PageSpeed] 18

Here is one way... Just pass the worksheet name of the sheet you want to 
format 
like Call FormatSheet("Sheet1")  When you want to format it.

Public Sub FormatWorksheet(ByVal SheetName As String)
  Const Grey = &HC0C0C0
  Dim ColumnArray As Variant
  Dim RowArray As Variant
  Dim I As Long
  Dim Ws As Worksheet
  
  Set Ws = Worksheets(SheetName)
  ColumnArray = Array(1, 3, 5, 7)
  RowArray = Array(1, 3)
  
  For I = LBound(ColumnArray) To UBound(ColumnArray)
    'Format Columns
    With Ws
      .Columns(ColumnArray(I)).ColumnWidth = 2
      .Columns(ColumnArray(I)).Interior.Color = Grey
    End With
  Next
      
  I = 0
  
  For I = LBound(RowArray) To UBound(RowArray)
    'Format Rows
    With Ws
      .Rows(RowArray(I)).RowHeight = 6
      .Rows(RowArray(I)).Interior.Color = Grey
    End With
  Next
   
End Sub


"KennyD" wrote:

> I am new to VBA and can follow along fairly well, but don't know how to do 
> certain things.  I have the following macro that runs like a champ, but now I 
> need to automatically format the SummarySheet.  Specifically, I need to set 
> Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in 
> Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1 
> and 3 to a height of 6 and have a color of Light Gray.  Then I need to change 
> the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the 
> bottom of Column F that says "Totals" and then sum all the values in Column H 
> from H4 to the LastRow.  Any help would be greatly appreciated.
> 
> Sub Summary_All_Worksheets_With_Formulas()
>     Dim Sh As Worksheet
>     Dim Newsh As Worksheet
>     Dim myCell As Range
>     Dim ColNum As Integer
>     Dim RwNum As Long
>     Dim Basebook As Workbook
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = False
>     End With
> 
>    'Delete the sheet "SummarySheet" if it exists
>     Application.DisplayAlerts = False
>     On Error Resume Next
>     ThisWorkbook.Worksheets("SummarySheet").Delete
>     On Error GoTo 0
>     Application.DisplayAlerts = True
> 
>     'Add a worksheet with the name "SummarySheet"
>     Set Basebook = ThisWorkbook
>     Set Newsh = Basebook.Worksheets.Add
>     Newsh.Name = "SummarySheet"
>     
>     'Add headers on row 2
>     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
> "", "Profitability", "", "Residuals")
> 
>     'The links to the first sheet will start in row 4
>     RwNum = 3
> 
>     For Each Sh In Basebook.Worksheets
>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>             ColNum = 2
>             RwNum = RwNum + 1
>             'Create a link to the sheet in the B column
>             Newsh.Cells(RwNum, 2).Formula = 
> "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
>             & """" & Sh.Name & """)"
>             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
>                 ColNum = ColNum + 2
>                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> myCell.Address(False, False)
>             Next myCell
> 
>         End If
>     Next Sh
> 
>     Newsh.UsedRange.Columns.AutoFit
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = True
>     End With
> End Sub
> -- 
> Nothing in life is ever easy - just get used to that fact.
0
Utf
1/29/2010 4:06:04 PM
Hi

Try this one:

Sub Summary_All_Worksheets_With_Formulas()
    Dim Sh As Worksheet
    Dim Newsh As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

   'Delete the sheet "SummarySheet" if it exists
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("SummarySheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "SummarySheet"
    Set Basebook = ThisWorkbook
    Set Newsh = Basebook.Worksheets.Add
    Newsh.Name = "SummarySheet"

    'Add headers on row 2
    Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
"", "Profitability", "", "Residuals")

    'The links to the first sheet will start in row 4
    RwNum = 3

    For Each Sh In Basebook.Worksheets
        If Sh.Name <> Newsh.Name And Sh.Visible Then
            ColNum = 2
            RwNum = RwNum + 1
            'Create a link to the sheet in the B column
            Newsh.Cells(RwNum, 2).Formula = 
"=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
            & """" & Sh.Name & """)"
            For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the 
range
                ColNum = ColNum + 2
                Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
myCell.Address(False, False)
            Next myCell

        End If
    Next Sh

    LastRow = Newsh.Range("F" & Rows.Count).End(xlUp).Row
    Newsh.Range("F" & LastRow + 1) = "Totals"
    Newsh.Range("H" & LastRow + 1).Formula = "SUM(H4:H" & LastRow & ")"
    Newsh.UsedRange.Columns.AutoFit
    Newsh.Columns("A,C,E,G").ColumnWidth = 2
    Newsh.Range("1,3").RowHeight = 6
    With Newsh.Range("1,3").Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With

    With Newsh.UsedRange.Font
        .Name = "Tahoma"
        .Size = 12
    End With

    Newsh.UsedRange.Font.Bold = True
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "Totals"
    Range("H16").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
    Range("H17").Select

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = True
    End With
End Sub

Regards,
Per

"KennyD" <KennyD@discussions.microsoft.com> skrev i meddelelsen 
news:FFC5EF7A-5514-4680-B577-57A110D7FDE6@microsoft.com...
>I am new to VBA and can follow along fairly well, but don't know how to do
> certain things.  I have the following macro that runs like a champ, but 
> now I
> need to automatically format the SummarySheet.  Specifically, I need to 
> set
> Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in
> Office 2010 it's White, Background 1, Darker 25%).  I also need to set 
> Rows 1
> and 3 to a height of 6 and have a color of Light Gray.  Then I need to 
> change
> the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at 
> the
> bottom of Column F that says "Totals" and then sum all the values in 
> Column H
> from H4 to the LastRow.  Any help would be greatly appreciated.
>
> Sub Summary_All_Worksheets_With_Formulas()
>    Dim Sh As Worksheet
>    Dim Newsh As Worksheet
>    Dim myCell As Range
>    Dim ColNum As Integer
>    Dim RwNum As Long
>    Dim Basebook As Workbook
>
>    With Application
>        .Calculation = xlCalculationManual
>        .ScreenUpdating = False
>    End With
>
>   'Delete the sheet "SummarySheet" if it exists
>    Application.DisplayAlerts = False
>    On Error Resume Next
>    ThisWorkbook.Worksheets("SummarySheet").Delete
>    On Error GoTo 0
>    Application.DisplayAlerts = True
>
>    'Add a worksheet with the name "SummarySheet"
>    Set Basebook = ThisWorkbook
>    Set Newsh = Basebook.Worksheets.Add
>    Newsh.Name = "SummarySheet"
>
>    'Add headers on row 2
>    Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID",
> "", "Profitability", "", "Residuals")
>
>    'The links to the first sheet will start in row 4
>    RwNum = 3
>
>    For Each Sh In Basebook.Worksheets
>        If Sh.Name <> Newsh.Name And Sh.Visible Then
>            ColNum = 2
>            RwNum = RwNum + 1
>            'Create a link to the sheet in the B column
>            Newsh.Cells(RwNum, 2).Formula =
> "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
>            & """" & Sh.Name & """)"
>            For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the 
> range
>                ColNum = ColNum + 2
>                Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" 
> &
> myCell.Address(False, False)
>            Next myCell
>
>        End If
>    Next Sh
>
>    Newsh.UsedRange.Columns.AutoFit
>
>    With Application
>        .Calculation = xlCalculationManual
>        .ScreenUpdating = True
>    End With
> End Sub
> -- 
> Nothing in life is ever easy - just get used to that fact. 

0
Per
1/29/2010 4:18:49 PM
You code have used the Macro Recorder for something like this.  I like to use 
it for simple macros which helps you identify certain objects and properties. 
 It also will help you learn a few things about VBA.  I added all the sheet 
formatting code at the end of your code.  Hope this helps!  If so, let me 
know, click "YES" below.

Sub Summary_All_Worksheets_With_Formulas()

Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook
Dim LastRow As Long

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

   'Delete the sheet "SummarySheet" if it exists
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("SummarySheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "SummarySheet"
    Set Basebook = ThisWorkbook
    Set Newsh = Basebook.Worksheets.Add
    Newsh.Name = "SummarySheet"
    
    'Add headers on row 2
    Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", _
                                            "", "Profitability", "", 
"Residuals")

    'The links to the first sheet will start in row 4
    RwNum = 3

    For Each Sh In Basebook.Worksheets
        If Sh.Name <> Newsh.Name And Sh.Visible Then
            ColNum = 2
            RwNum = RwNum + 1
            'Create a link to the sheet in the B column
            Newsh.Cells(RwNum, 2).Formula = 
"=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
            & """" & Sh.Name & """)"
            For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
                ColNum = ColNum + 2
                Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
myCell.Address(False, False)
            Next myCell

        End If
    Next Sh

    With Newsh
        .UsedRange.Columns.AutoFit
    
        'Columns A,C,E and G to a width of 2 and to have a color of Light 
Gray (in
        'Office 2010 it's White, Background 1, Darker 25%)
        With .Range("A:A,C:C,E:E,G:G")
            .ColumnWidth = 2
            .Interior.ColorIndex = 15
        End With
    
        ' I also need to set Rows 1 and 3 to a height of 6 and have a color 
of Light Gray.
        With .Range("1:1", "3:3")
            .RowHeight = 6
            .Interior.ColorIndex = 15
        End With
    
        ' sum column H
        LastRow = .Cells(Rows.Count, "F").End(xlUp).Row
        .Cells(LastRow + 1, "F").Value = "Totals"
        .Cells(LastRow + 1, "H").Formula = "=SUM(H4:H" & LastRow & ")"
    End With
    
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = True
    End With
End Sub
-- 
Cheers,
Ryan


"KennyD" wrote:

> I am new to VBA and can follow along fairly well, but don't know how to do 
> certain things.  I have the following macro that runs like a champ, but now I 
> need to automatically format the SummarySheet.  Specifically, I need to set 
> Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in 
> Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1 
> and 3 to a height of 6 and have a color of Light Gray.  Then I need to change 
> the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the 
> bottom of Column F that says "Totals" and then sum all the values in Column H 
> from H4 to the LastRow.  Any help would be greatly appreciated.
> 
> Sub Summary_All_Worksheets_With_Formulas()
>     Dim Sh As Worksheet
>     Dim Newsh As Worksheet
>     Dim myCell As Range
>     Dim ColNum As Integer
>     Dim RwNum As Long
>     Dim Basebook As Workbook
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = False
>     End With
> 
>    'Delete the sheet "SummarySheet" if it exists
>     Application.DisplayAlerts = False
>     On Error Resume Next
>     ThisWorkbook.Worksheets("SummarySheet").Delete
>     On Error GoTo 0
>     Application.DisplayAlerts = True
> 
>     'Add a worksheet with the name "SummarySheet"
>     Set Basebook = ThisWorkbook
>     Set Newsh = Basebook.Worksheets.Add
>     Newsh.Name = "SummarySheet"
>     
>     'Add headers on row 2
>     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
> "", "Profitability", "", "Residuals")
> 
>     'The links to the first sheet will start in row 4
>     RwNum = 3
> 
>     For Each Sh In Basebook.Worksheets
>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>             ColNum = 2
>             RwNum = RwNum + 1
>             'Create a link to the sheet in the B column
>             Newsh.Cells(RwNum, 2).Formula = 
> "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
>             & """" & Sh.Name & """)"
>             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
>                 ColNum = ColNum + 2
>                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> myCell.Address(False, False)
>             Next myCell
> 
>         End If
>     Next Sh
> 
>     Newsh.UsedRange.Columns.AutoFit
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = True
>     End With
> End Sub
> -- 
> Nothing in life is ever easy - just get used to that fact.
0
Utf
1/29/2010 4:33:01 PM
Thanks Jeff.  That worked like a champ.  But how do I add the word "Totals" 
in Column F one row after the last row (because the last row will be 
changing), and then sum the values in Column H?  Any thoughts on that?
-- 
Nothing in life is ever easy - just get used to that fact.


"Jeff" wrote:

> Here is one way... Just pass the worksheet name of the sheet you want to 
> format 
> like Call FormatSheet("Sheet1")  When you want to format it.
> 
> Public Sub FormatWorksheet(ByVal SheetName As String)
>   Const Grey = &HC0C0C0
>   Dim ColumnArray As Variant
>   Dim RowArray As Variant
>   Dim I As Long
>   Dim Ws As Worksheet
>   
>   Set Ws = Worksheets(SheetName)
>   ColumnArray = Array(1, 3, 5, 7)
>   RowArray = Array(1, 3)
>   
>   For I = LBound(ColumnArray) To UBound(ColumnArray)
>     'Format Columns
>     With Ws
>       .Columns(ColumnArray(I)).ColumnWidth = 2
>       .Columns(ColumnArray(I)).Interior.Color = Grey
>     End With
>   Next
>       
>   I = 0
>   
>   For I = LBound(RowArray) To UBound(RowArray)
>     'Format Rows
>     With Ws
>       .Rows(RowArray(I)).RowHeight = 6
>       .Rows(RowArray(I)).Interior.Color = Grey
>     End With
>   Next
>    
> End Sub
> 
> 
> "KennyD" wrote:
> 
> > I am new to VBA and can follow along fairly well, but don't know how to do 
> > certain things.  I have the following macro that runs like a champ, but now I 
> > need to automatically format the SummarySheet.  Specifically, I need to set 
> > Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in 
> > Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1 
> > and 3 to a height of 6 and have a color of Light Gray.  Then I need to change 
> > the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the 
> > bottom of Column F that says "Totals" and then sum all the values in Column H 
> > from H4 to the LastRow.  Any help would be greatly appreciated.
> > 
> > Sub Summary_All_Worksheets_With_Formulas()
> >     Dim Sh As Worksheet
> >     Dim Newsh As Worksheet
> >     Dim myCell As Range
> >     Dim ColNum As Integer
> >     Dim RwNum As Long
> >     Dim Basebook As Workbook
> > 
> >     With Application
> >         .Calculation = xlCalculationManual
> >         .ScreenUpdating = False
> >     End With
> > 
> >    'Delete the sheet "SummarySheet" if it exists
> >     Application.DisplayAlerts = False
> >     On Error Resume Next
> >     ThisWorkbook.Worksheets("SummarySheet").Delete
> >     On Error GoTo 0
> >     Application.DisplayAlerts = True
> > 
> >     'Add a worksheet with the name "SummarySheet"
> >     Set Basebook = ThisWorkbook
> >     Set Newsh = Basebook.Worksheets.Add
> >     Newsh.Name = "SummarySheet"
> >     
> >     'Add headers on row 2
> >     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
> > "", "Profitability", "", "Residuals")
> > 
> >     'The links to the first sheet will start in row 4
> >     RwNum = 3
> > 
> >     For Each Sh In Basebook.Worksheets
> >         If Sh.Name <> Newsh.Name And Sh.Visible Then
> >             ColNum = 2
> >             RwNum = RwNum + 1
> >             'Create a link to the sheet in the B column
> >             Newsh.Cells(RwNum, 2).Formula = 
> > "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
> >             & """" & Sh.Name & """)"
> >             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
> >                 ColNum = ColNum + 2
> >                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> > myCell.Address(False, False)
> >             Next myCell
> > 
> >         End If
> >     Next Sh
> > 
> >     Newsh.UsedRange.Columns.AutoFit
> > 
> >     With Application
> >         .Calculation = xlCalculationManual
> >         .ScreenUpdating = True
> >     End With
> > End Sub
> > -- 
> > Nothing in life is ever easy - just get used to that fact.
0
Utf
1/29/2010 4:34:01 PM
Check my code out in the previous post!  Hope this helps!  If so, let me 
know, click "YES" below.
-- 
Cheers,
Ryan


"KennyD" wrote:

> Thanks Jeff.  That worked like a champ.  But how do I add the word "Totals" 
> in Column F one row after the last row (because the last row will be 
> changing), and then sum the values in Column H?  Any thoughts on that?
> -- 
> Nothing in life is ever easy - just get used to that fact.
> 
> 
> "Jeff" wrote:
> 
> > Here is one way... Just pass the worksheet name of the sheet you want to 
> > format 
> > like Call FormatSheet("Sheet1")  When you want to format it.
> > 
> > Public Sub FormatWorksheet(ByVal SheetName As String)
> >   Const Grey = &HC0C0C0
> >   Dim ColumnArray As Variant
> >   Dim RowArray As Variant
> >   Dim I As Long
> >   Dim Ws As Worksheet
> >   
> >   Set Ws = Worksheets(SheetName)
> >   ColumnArray = Array(1, 3, 5, 7)
> >   RowArray = Array(1, 3)
> >   
> >   For I = LBound(ColumnArray) To UBound(ColumnArray)
> >     'Format Columns
> >     With Ws
> >       .Columns(ColumnArray(I)).ColumnWidth = 2
> >       .Columns(ColumnArray(I)).Interior.Color = Grey
> >     End With
> >   Next
> >       
> >   I = 0
> >   
> >   For I = LBound(RowArray) To UBound(RowArray)
> >     'Format Rows
> >     With Ws
> >       .Rows(RowArray(I)).RowHeight = 6
> >       .Rows(RowArray(I)).Interior.Color = Grey
> >     End With
> >   Next
> >    
> > End Sub
> > 
> > 
> > "KennyD" wrote:
> > 
> > > I am new to VBA and can follow along fairly well, but don't know how to do 
> > > certain things.  I have the following macro that runs like a champ, but now I 
> > > need to automatically format the SummarySheet.  Specifically, I need to set 
> > > Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in 
> > > Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1 
> > > and 3 to a height of 6 and have a color of Light Gray.  Then I need to change 
> > > the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the 
> > > bottom of Column F that says "Totals" and then sum all the values in Column H 
> > > from H4 to the LastRow.  Any help would be greatly appreciated.
> > > 
> > > Sub Summary_All_Worksheets_With_Formulas()
> > >     Dim Sh As Worksheet
> > >     Dim Newsh As Worksheet
> > >     Dim myCell As Range
> > >     Dim ColNum As Integer
> > >     Dim RwNum As Long
> > >     Dim Basebook As Workbook
> > > 
> > >     With Application
> > >         .Calculation = xlCalculationManual
> > >         .ScreenUpdating = False
> > >     End With
> > > 
> > >    'Delete the sheet "SummarySheet" if it exists
> > >     Application.DisplayAlerts = False
> > >     On Error Resume Next
> > >     ThisWorkbook.Worksheets("SummarySheet").Delete
> > >     On Error GoTo 0
> > >     Application.DisplayAlerts = True
> > > 
> > >     'Add a worksheet with the name "SummarySheet"
> > >     Set Basebook = ThisWorkbook
> > >     Set Newsh = Basebook.Worksheets.Add
> > >     Newsh.Name = "SummarySheet"
> > >     
> > >     'Add headers on row 2
> > >     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
> > > "", "Profitability", "", "Residuals")
> > > 
> > >     'The links to the first sheet will start in row 4
> > >     RwNum = 3
> > > 
> > >     For Each Sh In Basebook.Worksheets
> > >         If Sh.Name <> Newsh.Name And Sh.Visible Then
> > >             ColNum = 2
> > >             RwNum = RwNum + 1
> > >             'Create a link to the sheet in the B column
> > >             Newsh.Cells(RwNum, 2).Formula = 
> > > "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
> > >             & """" & Sh.Name & """)"
> > >             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
> > >                 ColNum = ColNum + 2
> > >                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> > > myCell.Address(False, False)
> > >             Next myCell
> > > 
> > >         End If
> > >     Next Sh
> > > 
> > >     Newsh.UsedRange.Columns.AutoFit
> > > 
> > >     With Application
> > >         .Calculation = xlCalculationManual
> > >         .ScreenUpdating = True
> > >     End With
> > > End Sub
> > > -- 
> > > Nothing in life is ever easy - just get used to that fact.
0
Utf
1/29/2010 4:39:07 PM
 Public Sub FormatWorksheet(ByVal SheetName As String)
   Const Grey = &HC0C0C0
   Dim LastRow As Long
   Dim ColumnArray As Variant
   Dim RowArray As Variant
   Dim I As Long
   Dim Ws As Worksheet

   Set Ws = Worksheets(SheetName)
   ColumnArray = Array(1, 3, 5, 7)
   RowArray = Array(1, 3)
   LastRow = Ws.Cells(Rows.Count, 6).End(xlUp).Row + 1
   
   
   For I = LBound(ColumnArray) To UBound(ColumnArray)
     'Format Columns
     With Ws
       .Columns(ColumnArray(I)).ColumnWidth = 2
       .Columns(ColumnArray(I)).Interior.Color = Grey
     End With
   Next

   I = 0

   For I = LBound(RowArray) To UBound(RowArray)
     'Format Rows
     With Ws
       .Rows(RowArray(I)).RowHeight = 6
       .Rows(RowArray(I)).Interior.Color = Grey
     End With
   Next
   
   Ws.Range("F" & LastRow).Value = "Totals:"
 End Sub

"Jeff" wrote:

> Here is one way... Just pass the worksheet name of the sheet you want to 
> format 
> like Call FormatSheet("Sheet1")  When you want to format it.
> 
> Public Sub FormatWorksheet(ByVal SheetName As String)
>   Const Grey = &HC0C0C0
>   Dim ColumnArray As Variant
>   Dim RowArray As Variant
>   Dim I As Long
>   Dim Ws As Worksheet
>   
>   Set Ws = Worksheets(SheetName)
>   ColumnArray = Array(1, 3, 5, 7)
>   RowArray = Array(1, 3)
>   
>   For I = LBound(ColumnArray) To UBound(ColumnArray)
>     'Format Columns
>     With Ws
>       .Columns(ColumnArray(I)).ColumnWidth = 2
>       .Columns(ColumnArray(I)).Interior.Color = Grey
>     End With
>   Next
>       
>   I = 0
>   
>   For I = LBound(RowArray) To UBound(RowArray)
>     'Format Rows
>     With Ws
>       .Rows(RowArray(I)).RowHeight = 6
>       .Rows(RowArray(I)).Interior.Color = Grey
>     End With
>   Next
>    
> End Sub
> 
> 
> "KennyD" wrote:
> 
> > I am new to VBA and can follow along fairly well, but don't know how to do 
> > certain things.  I have the following macro that runs like a champ, but now I 
> > need to automatically format the SummarySheet.  Specifically, I need to set 
> > Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in 
> > Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1 
> > and 3 to a height of 6 and have a color of Light Gray.  Then I need to change 
> > the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the 
> > bottom of Column F that says "Totals" and then sum all the values in Column H 
> > from H4 to the LastRow.  Any help would be greatly appreciated.
> > 
> > Sub Summary_All_Worksheets_With_Formulas()
> >     Dim Sh As Worksheet
> >     Dim Newsh As Worksheet
> >     Dim myCell As Range
> >     Dim ColNum As Integer
> >     Dim RwNum As Long
> >     Dim Basebook As Workbook
> > 
> >     With Application
> >         .Calculation = xlCalculationManual
> >         .ScreenUpdating = False
> >     End With
> > 
> >    'Delete the sheet "SummarySheet" if it exists
> >     Application.DisplayAlerts = False
> >     On Error Resume Next
> >     ThisWorkbook.Worksheets("SummarySheet").Delete
> >     On Error GoTo 0
> >     Application.DisplayAlerts = True
> > 
> >     'Add a worksheet with the name "SummarySheet"
> >     Set Basebook = ThisWorkbook
> >     Set Newsh = Basebook.Worksheets.Add
> >     Newsh.Name = "SummarySheet"
> >     
> >     'Add headers on row 2
> >     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
> > "", "Profitability", "", "Residuals")
> > 
> >     'The links to the first sheet will start in row 4
> >     RwNum = 3
> > 
> >     For Each Sh In Basebook.Worksheets
> >         If Sh.Name <> Newsh.Name And Sh.Visible Then
> >             ColNum = 2
> >             RwNum = RwNum + 1
> >             'Create a link to the sheet in the B column
> >             Newsh.Cells(RwNum, 2).Formula = 
> > "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
> >             & """" & Sh.Name & """)"
> >             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
> >                 ColNum = ColNum + 2
> >                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> > myCell.Address(False, False)
> >             Next myCell
> > 
> >         End If
> >     Next Sh
> > 
> >     Newsh.UsedRange.Columns.AutoFit
> > 
> >     With Application
> >         .Calculation = xlCalculationManual
> >         .ScreenUpdating = True
> >     End With
> > End Sub
> > -- 
> > Nothing in life is ever easy - just get used to that fact.
0
Utf
1/29/2010 4:45:02 PM
Add this  & WorksheetFunction.Sum(Range("H:H"))
After this   Ws.Range("F" & LastRow).Value = "Totals:"
to add Column "H"

"KennyD" wrote:

> Thanks Jeff.  That worked like a champ.  But how do I add the word "Totals" 
> in Column F one row after the last row (because the last row will be 
> changing), and then sum the values in Column H?  Any thoughts on that?
> -- 
> Nothing in life is ever easy - just get used to that fact.
> 
> 
> "Jeff" wrote:
> 
> > Here is one way... Just pass the worksheet name of the sheet you want to 
> > format 
> > like Call FormatSheet("Sheet1")  When you want to format it.
> > 
> > Public Sub FormatWorksheet(ByVal SheetName As String)
> >   Const Grey = &HC0C0C0
> >   Dim ColumnArray As Variant
> >   Dim RowArray As Variant
> >   Dim I As Long
> >   Dim Ws As Worksheet
> >   
> >   Set Ws = Worksheets(SheetName)
> >   ColumnArray = Array(1, 3, 5, 7)
> >   RowArray = Array(1, 3)
> >   
> >   For I = LBound(ColumnArray) To UBound(ColumnArray)
> >     'Format Columns
> >     With Ws
> >       .Columns(ColumnArray(I)).ColumnWidth = 2
> >       .Columns(ColumnArray(I)).Interior.Color = Grey
> >     End With
> >   Next
> >       
> >   I = 0
> >   
> >   For I = LBound(RowArray) To UBound(RowArray)
> >     'Format Rows
> >     With Ws
> >       .Rows(RowArray(I)).RowHeight = 6
> >       .Rows(RowArray(I)).Interior.Color = Grey
> >     End With
> >   Next
> >    
> > End Sub
> > 
> > 
> > "KennyD" wrote:
> > 
> > > I am new to VBA and can follow along fairly well, but don't know how to do 
> > > certain things.  I have the following macro that runs like a champ, but now I 
> > > need to automatically format the SummarySheet.  Specifically, I need to set 
> > > Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in 
> > > Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1 
> > > and 3 to a height of 6 and have a color of Light Gray.  Then I need to change 
> > > the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the 
> > > bottom of Column F that says "Totals" and then sum all the values in Column H 
> > > from H4 to the LastRow.  Any help would be greatly appreciated.
> > > 
> > > Sub Summary_All_Worksheets_With_Formulas()
> > >     Dim Sh As Worksheet
> > >     Dim Newsh As Worksheet
> > >     Dim myCell As Range
> > >     Dim ColNum As Integer
> > >     Dim RwNum As Long
> > >     Dim Basebook As Workbook
> > > 
> > >     With Application
> > >         .Calculation = xlCalculationManual
> > >         .ScreenUpdating = False
> > >     End With
> > > 
> > >    'Delete the sheet "SummarySheet" if it exists
> > >     Application.DisplayAlerts = False
> > >     On Error Resume Next
> > >     ThisWorkbook.Worksheets("SummarySheet").Delete
> > >     On Error GoTo 0
> > >     Application.DisplayAlerts = True
> > > 
> > >     'Add a worksheet with the name "SummarySheet"
> > >     Set Basebook = ThisWorkbook
> > >     Set Newsh = Basebook.Worksheets.Add
> > >     Newsh.Name = "SummarySheet"
> > >     
> > >     'Add headers on row 2
> > >     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
> > > "", "Profitability", "", "Residuals")
> > > 
> > >     'The links to the first sheet will start in row 4
> > >     RwNum = 3
> > > 
> > >     For Each Sh In Basebook.Worksheets
> > >         If Sh.Name <> Newsh.Name And Sh.Visible Then
> > >             ColNum = 2
> > >             RwNum = RwNum + 1
> > >             'Create a link to the sheet in the B column
> > >             Newsh.Cells(RwNum, 2).Formula = 
> > > "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
> > >             & """" & Sh.Name & """)"
> > >             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
> > >                 ColNum = ColNum + 2
> > >                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> > > myCell.Address(False, False)
> > >             Next myCell
> > > 
> > >         End If
> > >     Next Sh
> > > 
> > >     Newsh.UsedRange.Columns.AutoFit
> > > 
> > >     With Application
> > >         .Calculation = xlCalculationManual
> > >         .ScreenUpdating = True
> > >     End With
> > > End Sub
> > > -- 
> > > Nothing in life is ever easy - just get used to that fact.
0
Utf
1/29/2010 4:58:02 PM
It keeps giving me an Unqualified error at the very last section.

         ' sum column H
>         LastRow = .Cells(Rows.Count, "F").End(xlUp).Row
>         .Cells(LastRow + 1, "F").Value = "Totals"
>         .Cells(LastRow + 1, "H").Formula = "=SUM(H4:H" & LastRow & ")"
>     End With
>     
-- 
Nothing in life is ever easy - just get used to that fact.


"Ryan H" wrote:

> You code have used the Macro Recorder for something like this.  I like to use 
> it for simple macros which helps you identify certain objects and properties. 
>  It also will help you learn a few things about VBA.  I added all the sheet 
> formatting code at the end of your code.  Hope this helps!  If so, let me 
> know, click "YES" below.
> 
> Sub Summary_All_Worksheets_With_Formulas()
> 
> Dim Sh As Worksheet
> Dim Newsh As Worksheet
> Dim myCell As Range
> Dim ColNum As Integer
> Dim RwNum As Long
> Dim Basebook As Workbook
> Dim LastRow As Long
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = False
>     End With
> 
>    'Delete the sheet "SummarySheet" if it exists
>     Application.DisplayAlerts = False
>     On Error Resume Next
>     ThisWorkbook.Worksheets("SummarySheet").Delete
>     On Error GoTo 0
>     Application.DisplayAlerts = True
> 
>     'Add a worksheet with the name "SummarySheet"
>     Set Basebook = ThisWorkbook
>     Set Newsh = Basebook.Worksheets.Add
>     Newsh.Name = "SummarySheet"
>     
>     'Add headers on row 2
>     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", _
>                                             "", "Profitability", "", 
> "Residuals")
> 
>     'The links to the first sheet will start in row 4
>     RwNum = 3
> 
>     For Each Sh In Basebook.Worksheets
>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>             ColNum = 2
>             RwNum = RwNum + 1
>             'Create a link to the sheet in the B column
>             Newsh.Cells(RwNum, 2).Formula = 
> "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
>             & """" & Sh.Name & """)"
>             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
>                 ColNum = ColNum + 2
>                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> myCell.Address(False, False)
>             Next myCell
> 
>         End If
>     Next Sh
> 
>     With Newsh
>         .UsedRange.Columns.AutoFit
>     
>         'Columns A,C,E and G to a width of 2 and to have a color of Light 
> Gray (in
>         'Office 2010 it's White, Background 1, Darker 25%)
>         With .Range("A:A,C:C,E:E,G:G")
>             .ColumnWidth = 2
>             .Interior.ColorIndex = 15
>         End With
>     
>         ' I also need to set Rows 1 and 3 to a height of 6 and have a color 
> of Light Gray.
>         With .Range("1:1", "3:3")
>             .RowHeight = 6
>             .Interior.ColorIndex = 15
>         End With
>     
>         ' sum column H
>         LastRow = .Cells(Rows.Count, "F").End(xlUp).Row
>         .Cells(LastRow + 1, "F").Value = "Totals"
>         .Cells(LastRow + 1, "H").Formula = "=SUM(H4:H" & LastRow & ")"
>     End With
>     
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = True
>     End With
> End Sub
> -- 
> Cheers,
> Ryan
> 
> 
> "KennyD" wrote:
> 
> > I am new to VBA and can follow along fairly well, but don't know how to do 
> > certain things.  I have the following macro that runs like a champ, but now I 
> > need to automatically format the SummarySheet.  Specifically, I need to set 
> > Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in 
> > Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1 
> > and 3 to a height of 6 and have a color of Light Gray.  Then I need to change 
> > the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the 
> > bottom of Column F that says "Totals" and then sum all the values in Column H 
> > from H4 to the LastRow.  Any help would be greatly appreciated.
> > 
> > Sub Summary_All_Worksheets_With_Formulas()
> >     Dim Sh As Worksheet
> >     Dim Newsh As Worksheet
> >     Dim myCell As Range
> >     Dim ColNum As Integer
> >     Dim RwNum As Long
> >     Dim Basebook As Workbook
> > 
> >     With Application
> >         .Calculation = xlCalculationManual
> >         .ScreenUpdating = False
> >     End With
> > 
> >    'Delete the sheet "SummarySheet" if it exists
> >     Application.DisplayAlerts = False
> >     On Error Resume Next
> >     ThisWorkbook.Worksheets("SummarySheet").Delete
> >     On Error GoTo 0
> >     Application.DisplayAlerts = True
> > 
> >     'Add a worksheet with the name "SummarySheet"
> >     Set Basebook = ThisWorkbook
> >     Set Newsh = Basebook.Worksheets.Add
> >     Newsh.Name = "SummarySheet"
> >     
> >     'Add headers on row 2
> >     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
> > "", "Profitability", "", "Residuals")
> > 
> >     'The links to the first sheet will start in row 4
> >     RwNum = 3
> > 
> >     For Each Sh In Basebook.Worksheets
> >         If Sh.Name <> Newsh.Name And Sh.Visible Then
> >             ColNum = 2
> >             RwNum = RwNum + 1
> >             'Create a link to the sheet in the B column
> >             Newsh.Cells(RwNum, 2).Formula = 
> > "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
> >             & """" & Sh.Name & """)"
> >             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
> >                 ColNum = ColNum + 2
> >                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> > myCell.Address(False, False)
> >             Next myCell
> > 
> >         End If
> >     Next Sh
> > 
> >     Newsh.UsedRange.Columns.AutoFit
> > 
> >     With Application
> >         .Calculation = xlCalculationManual
> >         .ScreenUpdating = True
> >     End With
> > End Sub
> > -- 
> > Nothing in life is ever easy - just get used to that fact.
0
Utf
1/29/2010 5:17:01 PM
Check your other post, too.

KennyD wrote:
> 
> I am new to VBA and can follow along fairly well, but don't know how to do
> certain things.  I have the following macro that runs like a champ, but now I
> need to automatically format the SummarySheet.  Specifically, I need to set
> Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in
> Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1
> and 3 to a height of 6 and have a color of Light Gray.  Then I need to change
> the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the
> bottom of Column F that says "Totals" and then sum all the values in Column H
> from H4 to the LastRow.  Any help would be greatly appreciated.
> 
> Sub Summary_All_Worksheets_With_Formulas()
>     Dim Sh As Worksheet
>     Dim Newsh As Worksheet
>     Dim myCell As Range
>     Dim ColNum As Integer
>     Dim RwNum As Long
>     Dim Basebook As Workbook
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = False
>     End With
> 
>    'Delete the sheet "SummarySheet" if it exists
>     Application.DisplayAlerts = False
>     On Error Resume Next
>     ThisWorkbook.Worksheets("SummarySheet").Delete
>     On Error GoTo 0
>     Application.DisplayAlerts = True
> 
>     'Add a worksheet with the name "SummarySheet"
>     Set Basebook = ThisWorkbook
>     Set Newsh = Basebook.Worksheets.Add
>     Newsh.Name = "SummarySheet"
> 
>     'Add headers on row 2
>     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID",
> "", "Profitability", "", "Residuals")
> 
>     'The links to the first sheet will start in row 4
>     RwNum = 3
> 
>     For Each Sh In Basebook.Worksheets
>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>             ColNum = 2
>             RwNum = RwNum + 1
>             'Create a link to the sheet in the B column
>             Newsh.Cells(RwNum, 2).Formula =
> "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
>             & """" & Sh.Name & """)"
>             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
>                 ColNum = ColNum + 2
>                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" &
> myCell.Address(False, False)
>             Next myCell
> 
>         End If
>     Next Sh
> 
>     Newsh.UsedRange.Columns.AutoFit
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = True
>     End With
> End Sub
> --
> Nothing in life is ever easy - just get used to that fact.

-- 

Dave Peterson
0
Dave
1/29/2010 5:24:27 PM
Per,

Thank you.  This worked exactly like I wanted.  Awesome.  The only thing 
that I changed was that it now reads:
Newsh.Range("F" & (LastRow + 2)) = "Totals"

Then I threw in a little formatting and what not.  
-- 
Nothing in life is ever easy - just get used to that fact.


"Per Jessen" wrote:

> Hi
> 
> Try this one:
> 
> Sub Summary_All_Worksheets_With_Formulas()
>     Dim Sh As Worksheet
>     Dim Newsh As Worksheet
>     Dim myCell As Range
>     Dim ColNum As Integer
>     Dim RwNum As Long
>     Dim Basebook As Workbook
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = False
>     End With
> 
>    'Delete the sheet "SummarySheet" if it exists
>     Application.DisplayAlerts = False
>     On Error Resume Next
>     ThisWorkbook.Worksheets("SummarySheet").Delete
>     On Error GoTo 0
>     Application.DisplayAlerts = True
> 
>     'Add a worksheet with the name "SummarySheet"
>     Set Basebook = ThisWorkbook
>     Set Newsh = Basebook.Worksheets.Add
>     Newsh.Name = "SummarySheet"
> 
>     'Add headers on row 2
>     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID", 
> "", "Profitability", "", "Residuals")
> 
>     'The links to the first sheet will start in row 4
>     RwNum = 3
> 
>     For Each Sh In Basebook.Worksheets
>         If Sh.Name <> Newsh.Name And Sh.Visible Then
>             ColNum = 2
>             RwNum = RwNum + 1
>             'Create a link to the sheet in the B column
>             Newsh.Cells(RwNum, 2).Formula = 
> "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
>             & """" & Sh.Name & """)"
>             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the 
> range
>                 ColNum = ColNum + 2
>                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" & 
> myCell.Address(False, False)
>             Next myCell
> 
>         End If
>     Next Sh
> 
>     LastRow = Newsh.Range("F" & Rows.Count).End(xlUp).Row
>     Newsh.Range("F" & LastRow + 1) = "Totals"
>     Newsh.Range("H" & LastRow + 1).Formula = "SUM(H4:H" & LastRow & ")"
>     Newsh.UsedRange.Columns.AutoFit
>     Newsh.Columns("A,C,E,G").ColumnWidth = 2
>     Newsh.Range("1,3").RowHeight = 6
>     With Newsh.Range("1,3").Interior
>         .PatternColorIndex = xlAutomatic
>         .ThemeColor = xlThemeColorDark1
>         .TintAndShade = -0.249977111117893
>         .PatternTintAndShade = 0
>     End With
> 
>     With Newsh.UsedRange.Font
>         .Name = "Tahoma"
>         .Size = 12
>     End With
> 
>     Newsh.UsedRange.Font.Bold = True
>     Range("F16").Select
>     ActiveCell.FormulaR1C1 = "Totals"
>     Range("H16").Select
>     ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"
>     Range("H17").Select
> 
>     With Application
>         .Calculation = xlCalculationManual
>         .ScreenUpdating = True
>     End With
> End Sub
> 
> Regards,
> Per
> 
> "KennyD" <KennyD@discussions.microsoft.com> skrev i meddelelsen 
> news:FFC5EF7A-5514-4680-B577-57A110D7FDE6@microsoft.com...
> >I am new to VBA and can follow along fairly well, but don't know how to do
> > certain things.  I have the following macro that runs like a champ, but 
> > now I
> > need to automatically format the SummarySheet.  Specifically, I need to 
> > set
> > Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in
> > Office 2010 it's White, Background 1, Darker 25%).  I also need to set 
> > Rows 1
> > and 3 to a height of 6 and have a color of Light Gray.  Then I need to 
> > change
> > the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at 
> > the
> > bottom of Column F that says "Totals" and then sum all the values in 
> > Column H
> > from H4 to the LastRow.  Any help would be greatly appreciated.
> >
> > Sub Summary_All_Worksheets_With_Formulas()
> >    Dim Sh As Worksheet
> >    Dim Newsh As Worksheet
> >    Dim myCell As Range
> >    Dim ColNum As Integer
> >    Dim RwNum As Long
> >    Dim Basebook As Workbook
> >
> >    With Application
> >        .Calculation = xlCalculationManual
> >        .ScreenUpdating = False
> >    End With
> >
> >   'Delete the sheet "SummarySheet" if it exists
> >    Application.DisplayAlerts = False
> >    On Error Resume Next
> >    ThisWorkbook.Worksheets("SummarySheet").Delete
> >    On Error GoTo 0
> >    Application.DisplayAlerts = True
> >
> >    'Add a worksheet with the name "SummarySheet"
> >    Set Basebook = ThisWorkbook
> >    Set Newsh = Basebook.Worksheets.Add
> >    Newsh.Name = "SummarySheet"
> >
> >    'Add headers on row 2
> >    Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID",
> > "", "Profitability", "", "Residuals")
> >
> >    'The links to the first sheet will start in row 4
> >    RwNum = 3
> >
> >    For Each Sh In Basebook.Worksheets
> >        If Sh.Name <> Newsh.Name And Sh.Visible Then
> >            ColNum = 2
> >            RwNum = RwNum + 1
> >            'Create a link to the sheet in the B column
> >            Newsh.Cells(RwNum, 2).Formula =
> > "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
> >            & """" & Sh.Name & """)"
> >            For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the 
> > range
> >                ColNum = ColNum + 2
> >                Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" 
> > &
> > myCell.Address(False, False)
> >            Next myCell
> >
> >        End If
> >    Next Sh
> >
> >    Newsh.UsedRange.Columns.AutoFit
> >
> >    With Application
> >        .Calculation = xlCalculationManual
> >        .ScreenUpdating = True
> >    End With
> > End Sub
> > -- 
> > Nothing in life is ever easy - just get used to that fact. 
> 
> .
> 
0
Utf
1/29/2010 6:42:01 PM
I know - I had it in there twice.  The first was under the General Questions, 
but then realized that I should have posted it under the Programming section. 
 If you have a minute, can you look at the following post?

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&mid=31cc76fe-95c2-4593-8ac8-003799f591e7&sloc=en-us

This post was based on a macro that you had written awhile ago.   Thanks.
-- 
Nothing in life is ever easy - just get used to that fact.


"Dave Peterson" wrote:

> Check your other post, too.
> 
> KennyD wrote:
> > 
> > I am new to VBA and can follow along fairly well, but don't know how to do
> > certain things.  I have the following macro that runs like a champ, but now I
> > need to automatically format the SummarySheet.  Specifically, I need to set
> > Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in
> > Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1
> > and 3 to a height of 6 and have a color of Light Gray.  Then I need to change
> > the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the
> > bottom of Column F that says "Totals" and then sum all the values in Column H
> > from H4 to the LastRow.  Any help would be greatly appreciated.
> > 
> > Sub Summary_All_Worksheets_With_Formulas()
> >     Dim Sh As Worksheet
> >     Dim Newsh As Worksheet
> >     Dim myCell As Range
> >     Dim ColNum As Integer
> >     Dim RwNum As Long
> >     Dim Basebook As Workbook
> > 
> >     With Application
> >         .Calculation = xlCalculationManual
> >         .ScreenUpdating = False
> >     End With
> > 
> >    'Delete the sheet "SummarySheet" if it exists
> >     Application.DisplayAlerts = False
> >     On Error Resume Next
> >     ThisWorkbook.Worksheets("SummarySheet").Delete
> >     On Error GoTo 0
> >     Application.DisplayAlerts = True
> > 
> >     'Add a worksheet with the name "SummarySheet"
> >     Set Basebook = ThisWorkbook
> >     Set Newsh = Basebook.Worksheets.Add
> >     Newsh.Name = "SummarySheet"
> > 
> >     'Add headers on row 2
> >     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID",
> > "", "Profitability", "", "Residuals")
> > 
> >     'The links to the first sheet will start in row 4
> >     RwNum = 3
> > 
> >     For Each Sh In Basebook.Worksheets
> >         If Sh.Name <> Newsh.Name And Sh.Visible Then
> >             ColNum = 2
> >             RwNum = RwNum + 1
> >             'Create a link to the sheet in the B column
> >             Newsh.Cells(RwNum, 2).Formula =
> > "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
> >             & """" & Sh.Name & """)"
> >             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
> >                 ColNum = ColNum + 2
> >                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" &
> > myCell.Address(False, False)
> >             Next myCell
> > 
> >         End If
> >     Next Sh
> > 
> >     Newsh.UsedRange.Columns.AutoFit
> > 
> >     With Application
> >         .Calculation = xlCalculationManual
> >         .ScreenUpdating = True
> >     End With
> > End Sub
> > --
> > Nothing in life is ever easy - just get used to that fact.
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
1/29/2010 7:03:01 PM
Ron responded with to that post.

KennyD wrote:
> 
> I know - I had it in there twice.  The first was under the General Questions,
> but then realized that I should have posted it under the Programming section.
>  If you have a minute, can you look at the following post?
> 
> http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.misc&mid=31cc76fe-95c2-4593-8ac8-003799f591e7&sloc=en-us
> 
> This post was based on a macro that you had written awhile ago.   Thanks.
> --
> Nothing in life is ever easy - just get used to that fact.
> 
> "Dave Peterson" wrote:
> 
> > Check your other post, too.
> >
> > KennyD wrote:
> > >
> > > I am new to VBA and can follow along fairly well, but don't know how to do
> > > certain things.  I have the following macro that runs like a champ, but now I
> > > need to automatically format the SummarySheet.  Specifically, I need to set
> > > Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in
> > > Office 2010 it's White, Background 1, Darker 25%).  I also need to set Rows 1
> > > and 3 to a height of 6 and have a color of Light Gray.  Then I need to change
> > > the output font to Tahoma, 12, Bold.  Finally, I have to have a cell at the
> > > bottom of Column F that says "Totals" and then sum all the values in Column H
> > > from H4 to the LastRow.  Any help would be greatly appreciated.
> > >
> > > Sub Summary_All_Worksheets_With_Formulas()
> > >     Dim Sh As Worksheet
> > >     Dim Newsh As Worksheet
> > >     Dim myCell As Range
> > >     Dim ColNum As Integer
> > >     Dim RwNum As Long
> > >     Dim Basebook As Workbook
> > >
> > >     With Application
> > >         .Calculation = xlCalculationManual
> > >         .ScreenUpdating = False
> > >     End With
> > >
> > >    'Delete the sheet "SummarySheet" if it exists
> > >     Application.DisplayAlerts = False
> > >     On Error Resume Next
> > >     ThisWorkbook.Worksheets("SummarySheet").Delete
> > >     On Error GoTo 0
> > >     Application.DisplayAlerts = True
> > >
> > >     'Add a worksheet with the name "SummarySheet"
> > >     Set Basebook = ThisWorkbook
> > >     Set Newsh = Basebook.Worksheets.Add
> > >     Newsh.Name = "SummarySheet"
> > >
> > >     'Add headers on row 2
> > >     Newsh.Range("B2:H2").Value = Array("Merchant Name", "", "Merchant ID",
> > > "", "Profitability", "", "Residuals")
> > >
> > >     'The links to the first sheet will start in row 4
> > >     RwNum = 3
> > >
> > >     For Each Sh In Basebook.Worksheets
> > >         If Sh.Name <> Newsh.Name And Sh.Visible Then
> > >             ColNum = 2
> > >             RwNum = RwNum + 1
> > >             'Create a link to the sheet in the B column
> > >             Newsh.Cells(RwNum, 2).Formula =
> > > "=HYPERLINK(""#""&CELL(""address"",'" & Sh.Name & "'!A1)," _
> > >             & """" & Sh.Name & """)"
> > >             For Each myCell In Sh.Range("C3,T14,T15")    '<--Change the range
> > >                 ColNum = ColNum + 2
> > >                 Newsh.Cells(RwNum, ColNum).Formula = "='" & Sh.Name & "'!" &
> > > myCell.Address(False, False)
> > >             Next myCell
> > >
> > >         End If
> > >     Next Sh
> > >
> > >     Newsh.UsedRange.Columns.AutoFit
> > >
> > >     With Application
> > >         .Calculation = xlCalculationManual
> > >         .ScreenUpdating = True
> > >     End With
> > > End Sub
> > > --
> > > Nothing in life is ever easy - just get used to that fact.
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
1/29/2010 8:17:33 PM
Reply:

Similar Artilces:

Format a blank DVD disc doesn't work ???
I need to format a DVD disc but the "Burn to Disc" that Microsoft referred to when you insert the disc in the drive did not appear. Anyone know how I can do this with that program (I assumed it came with XP) or where I can get a format program. Thanks in advance for the help. I am using Win XP Sp2 w/2 GB Memory. Windows XP will not write to a DVD only CDs. Neil "Bubey" <catlov@earthlink.net> wrote in message news:eGMVHdikKHA.4500@TK2MSFTNGP06.phx.gbl... >I need to format a DVD disc but the "Burn to Disc" that Microsoft referred > ...

VBA Macro Error
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, I�m running in a pretty bad problem: If I write in MSOffice 2008 a number followed by a letter like "5a" on a new line I get an error "This command is not available in this Version of Microsoft Word" It seems that word want�s to make a lookup for a VBA script - which is not supported anymore, so in panic they write the message on the screen. But instead I really want to write only 5a. Is there a button to switch of those interpretations? It was not an issue with 2004 and not on windows. Any...

Formatting database downloaded from internet
Hi, all! I'd like to download into Excel a database from the internet. It's a list of newly formed business, with column headings being date filed, name, file #, and address. But the address ends up in two rows for each record - the first row has the street address, and the next row has the city, state and zip. I'd like to have the city, state, zip cell show up in the column to the right of the street address, instead of in the row underneath. Another complication is that it looks like the other fields - date, name, file # all take up two rows, because the two rows have bee...

Format email addresses?
Is there any way to format a column of email addresses, so if I click on an address it will open up a new message in my email client? -- The government is unresponsive to the needs of the little man. Under 5'7" it is impossible to get your congressman on the phone. ....Woody Allen Hi Steve You can run this macro for column A Sub test() For Each myCell In Columns("A").Cells.SpecialCells(xlCellTypeConstants) If myCell.Value Like "?*@?*.?*" Then ActiveSheet.Hyperlinks.Add Anchor:=myCell, _ Address:="mailto:" & ...

Modify existing custom cell format (Excel 2000)
I have a worksheet that includes a number of custom formats that are applied throughout to various cells. Is it possible to change an existing custom format so that all those cells that use it are automatically changed. For example, one of these formats is of type 0.00 "Deg" and I want to change it so that cells using this format show values with the degree symbol "�" instead. Since Excel doesn't seem to have a "find and replace" format feature, how can I make such a global change without individually selecting each applicable cell and applying a new format? ...

A way of disabling the Macro & Letter Writing Assistant in GP?
Would like a way within security of disabling the Macro & Letter Writing Assistant in Great Plains for certain users ------=_NextPart_0001_7140E14E Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello and thank you for using Newsgroups! There is not a way to disable or remove the security for the Macro features. For the Letter Writing Assistant, it pulls the templates from the local installation of Great Plains. In the install folder, you will see a LETTERS folder. If you remove or rename this folder, any user on this computer will not be able to use the Letter Writi...

Filter within a report
Hello all, I created a report based on a query. I would like the report's output to be adjusted according to filters that I use in the query. That is, when I filter some records in the query, I also would like to only see these particular records in my report. How can I achieve this with only creating one single report? Regards, Andreas If you are opening the query as a datasheet and applying a filter by such means as Filter by Form or Filter by Selection, then you won't be able to do this. If you open a form based on the query, however, and then filter the form you ca...

Trouble with Control Toolbox checkboxes and macros
Hello! I'm trying to create a form with checkboxes that utilize a "nested" behavior. For example, you can't mark the "Email Account" checkbox until you have first marked the "Network Account" checkbox. I see where I can manipulate the checkbox object's "enabled" property, but I'm unfortunately at a loss for the macro coding that would toggle that property. Does anyone know of a way to accomplish a nested checkbox behavior without incorporating macros? I'd like to be able to make this form without having to worry over macro securi...

Macro help
Hello I have recorded a macro to automatically import a file and then run a number of filters through it and to display the results in a separate worksheet. I do not have any Visual Basic knowledge, so am just using the "record macro" function. So far so good, but now I've run into a problem. How do I edit my macro so that it can automatically replace the contents of cells in a particular column in as many rows as are required? EXAMPLE: Let's say the macro runs a custom filter in Column X to show all the rows which have a value of either "A" orf "B" i...

Formatting email addresses
I keep several spread sheets of club membership, etc, and need to concentrate email addresses, but I do not want them to be hyperlinks. I have formatted the entire column as Text, but sometimes when I add a line, the formatting rebounds to some sort of default. I never want any hyperlinks in my worksheets EVER. Can someone please advise? TIA!! -- Regards, P D Sterling Dallas TX Try this: From the Excel main menu: <tools><options><spelling tab> Click the [AutoCorrect Options...] button Select the AutFormat as you Type tab UNcheck: Internet and network paths with hyper...

How to populate a dataset from an XML formated String
Hi there Here's my question : I'd like to populate a dataset from an XML string, ie: Dim sXmlString As String sXmlString = "<root type='E-Form'><question label='This is my first question'></question><question label='This is my second question'></question></root> Any idea to achieve this ? Thanks in advance, J.Philippe Sure... :) Take a look at the System.IO.StringReader and the DataSet.ReadXml Method.. Source code is from the following link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/h...

Excel changes macros when saving as pdf
Last week I had no problem saving my financial macros enable spreadsheet as a pdf. Now, forget it! It won't let me do anything...print, copy, cut, past, save as pdf, NOTHING without several of the worksheets changing the numbers to text tht says "VALUES" across the whole line. When Adobe opens the file, it says that my macros is unenabled when it is, and it lst's the "fixes" you're supposed to do in order to get it to work...which I've done over and over again. The security settings are correct and I've been over it countless times. I ...

Macro for DocumentStatistics: Contents of MsgBox in a new document
Dear Experts: I would like to paste the contents of the MsgBox into a new Document with the following heading: The document statistics for the current document <document name on which the macro was run> is as follows: (This heading should be wdStyleNormal and bold formatted). Each statisical data should be on its own paragraph (wdStyleNormal), not bold. Help is much appreciated. Thank you very much in advance. Regards, Andreas Public Sub DocumentStatistics() Dim rngReplace As Word.Range Dim rngFound As Word.Range Dim lngCharacters As Long Dim lngCharactersWithSp...

Multiple email accounts within OUTLOOK 2003
For a long time, as well as having my bigpong emails coming in, I have also had Outlook (2003) set up to receive email from a couple of Hotmail accounts. Some months ago (and I am sure it was after a Microsoft upgrade maybe in January), ALL emails I am sending appear to the receipient to have come from my bogpond account, NOT from the email account from which I intend to send them (within Outlook). When I look at the email addresses set up in the accounts, they are all OK. Any suggestions as to the problem resolution please? I am hoping this is not one of the added features of Outlook now!...

date format
I have just installed Money 04 on a new machine and uploaded my money file sucessfully. The date format is now US for all my accounts. Previously in my original money file, it was UK style. How do I change it back to UK style? Thank you. I think that the format comes from your computer settings. Check those first, if that doesn't work, post back. Cindy "abc" <noreply@mail.com> wrote in message news:O6Bm2tWiKHA.5596@TK2MSFTNGP05.phx.gbl... > I have just installed Money 04 on a new machine and uploaded my money file > sucessfully. > ...

Outlook 2007 calendar item formats
In my calendar, some items are listed with a small white border on the left hand side, and sometimes this border is striped. What do these borders mean? I have tried creating items with the exact same info and they don't have the white border, so I don't get what the difference is. Thanks. It's your show time as setting - Free is white, slashes are tentetive. -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists....

Record Macro Relative does not work? #2
Can someone help me with this macro. I want to go to any cell of my choice and press f2 to edit, then go to the fifth character of that cell, then select the next 6 characters and change them to a smaller font size (8), then "enter". I tried to make this macro using the record macro function, but it did not work how I want it to. It ends up pasting the contents of the cell which I used to record the macro initially. Can this kind of macro be done? lbbss Sub Change_Font() With ActiveCell.Characters(Start:=5, Length:=6).Font .Name = "Arial&quo...

Counting items within a date range
I need to request a little help please: I'm not fluent with writing formulas from scratch and need help (please): Using column B, I'd like to determine what was ordered by month and then categorize them by the item ordered. Ex: January and February, there was one for each item; in March there was 2 apples, 1 orange, 1 lemon and 1 pear; in April there was 1 apple, 2 oranges, 2 lemons and 2 pears. Column A Column B Ordered DateTime Apple 2008-01-04 15:32:18 Orange 2008-01-08 13:37:54 Lemon 2008-01-09 11:48:56 Pear 2008-01-09 16:33:48 Apple 2008-02-02 11:45:55 Orange 2008-...

DOES NOT EQUAL
Hi all, I'm trying to apply some conditional formatting to a range of cells where, if the cell DOES NOT EQUAL the value "FALSE" and another cell contains the word "Red" (there's more colors, but that's academic) then formatting occurs. In the conditional formatting box, I've entered the following formula: =(AND(D3<>"FALSE",$DA3="Red")) I thought the "<>" would indicate does not equal, or at least give the same results, but no joy. Any ideas? TIA, SamuelT -- SamuelT -----------------------------------------...

How to search for (and select) paragraphs with certain format parameters
I posted this in another thread, but the thread title dealt with a larger contextual problem, so it may go unnoticed. In Word 2003, I want to search for, and select, all paragraphs that conform to a list of attributes e.g. left indent of 0.53", hanging indent at 0.26", etc.. From the "Find and Replace" panel, I chose Format->Paragraph, and set the left indent to 0.53", etc.. Word did not find any of the paragraphs conforming to these attributes, even though they exist. Is there a way to search for (and select) paragraphs based on formatting parameters? ...

Macro for Sheet Navigation (Excel 2007)
I'm looking for a macro that is compatible with Excel 2007. I've searched the forums and shareware sites, but can't find anything current enough. I have a workbook with many sheets, too many to conveniently navigate through with just the basic Excel GUI. I have seen some older shareware apps that create floating menus that allow the user to organize the sheets into groups so that the user only has to interact with a subset of worksheets at any moment, based on the group that they have selected. This allows them to more easily switch between sheets without scrolling horizontally...

Loss of excel funtionality within IE
Hi there, I am having problem whereby when I open a excel file within a web page. When opened, the menus don't in IE do not work anymore. When I click on File, Edit, Data etc they do not respond to the click. Also the line showing the cell and it s contents is not available either. Would anyone know why this is happening. Can you tell me what I need to look for to ensure IE and Excel are talking to each other. Thanks for your time. I look forward to your replies. ...Bruce -- bruceweinstein ------------------------------------------------------------------------ bruceweinstein&#...

how do I export excel to a monthly calendar format?
I am on a Mac platform and use Entourage. It really isn't as comprehensive as we need. We need to view jobs in progress in a list and calendar format. If there isn't a export to calendar function, is there an extension available that works with excel and entourage? ...

Recording a macro ...
How can I use that "Relative Reference" when I'm recording a macro in Excel 2003? Just click it on to record relative references rather than hard-coded cell addresses. It will record code such as ActiveCell.Offset(5, 0).Select rather than Range("A8").Select And if you see the Stop Recording Toolbar but no Relative Reference button, you can "reset" the Toolbar under Tools>Customize>Toolbars. You may have dragged the button off. The usual cause of losing the entire Stop Recording Toolbar is turning it off when recording by using the "X&quo...

Macro that inserts lines
I have this macro that inserts a line if the value in D changes and copy the value in D to A. I want this macro to only start looking in D14 or from row 14. tx for all the help Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row For row_index = lastrow - 1 To 2 Step -1 If Cells(row_index, "D").Value <> _ Cells(row_index + 1, "D").Value Then Cells(row_index + 1, "D").EntireRow.Insert _ (xlShiftDown) Cells(row_index + 1, 1).Value = Cells(row_index + ...