Results 1 to 6 of 6

Add additional formatting to my macro

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    Add additional formatting to my macro

    Good day, Gurus. There have been several changes to the forum since I was here last. Most notabley applying "Code Tags" to show code. I checked the FAQ before posting, but it didn't tell me how to apply the code tags. I hope an Admin will tell me how to do this, and I will edit my messagebelow.

    The code below successfully separates my "Master" worksheet into different sheets depending on the entry in column 14.

    Sub Seperate_Struct_vs_Nonstruct()
    Dim rng2 As Range, rng As Range, ws As Worksheet
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    With Sheets("MASTER")
        .AutoFilterMode = False
    End With
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.Select
        With Selection.Font
            .Name = "Calibri"
            .Size = 9
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
    ActiveSheet.Name = "MASTER"
    ActiveWorkbook.Worksheets("MASTER").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("MASTER").Sort.SortFields.Add Key:= _
            Range("N1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortTextAsNumbers
        With ActiveWorkbook.Worksheets("MASTER").Sort
            .SetRange Range("A1:V" & Cells(Rows.Count, "A").End(xlUp).Row)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
    With Sheets("MASTER")
        Sheets.Add().Name = "Temp"
        .Range("N1", .Range("N1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
        Set rng = Sheets("Temp").Range("A2", Sheets("Temp").Range("A2").End(xlDown))
        For Each rng2 In rng
            .Range("A1").CurrentRegion.AutoFilter Field:=14, Criteria1:=rng2
            Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            .AutoFilter.Range.Copy ws.Range("A1")
            ws.Name = rng2
            .Range("A1").CurrentRegion.AutoFilter Field:=14
        Next rng2
        Sheets("Temp").Delete
        .AutoFilterMode = False
    End With
    Sheets("MASTER").Select
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Range("A1").Select
    End Sub
    I would like to add code that applies the following additional formatting to each sheet, but wasn't sure where to add it or the correect syntax, since I assumed it would be undert the "with sheets" statement.

    Sub Macro1()
        Rows("1:1").Copy 'Copies row 1 from the Master worksheet.
        Sheets("N").Select 'This is the sheet that was just created.
        Rows("1:1").Select
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False  'Copies the column width to tow 1.
        ActiveSheet.Paste
        Range("E2").Select
        ActiveWindow.FreezePanes = True  'Freezes panes on the selected sheet.
        Range("A1").Select  'Selects range A1 on the active sheet, then moves on to the next sheet.
    End Sub
    Thanks in advance for any help you can offer.
    Last edited by [email protected]; 02-15-2016 at 01:34 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 11-22-2013, 12:58 PM
  2. Additional Conditions for Conditional Formatting
    By David McRitchie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 07:05 AM
  3. [SOLVED] Additional Conditions for Conditional Formatting
    By David McRitchie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  4. Additional Conditions for Conditional Formatting
    By David McRitchie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  5. [SOLVED] Additional Conditions for Conditional Formatting
    By eric beck in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. Additional Conditions for Conditional Formatting
    By eric beck in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] Additional Conditions for Conditional Formatting
    By eric beck in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. Additional Conditions for Conditional Formatting
    By eric beck in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2005, 02:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1