Results 1 to 9 of 9

Command Button Not working in new file

Threaded View

  1. #3
    Forum Contributor
    Join Date
    11-11-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: Command Button Not working in new file

    Well it's a long code, hence I didn't really want to post it at first... (And I can't post all of it, as it's too long!! So I've posted the 2nd half of it in a separate post.)

    Basically it Unhides/Hides/Changes References throught about 20 worksheets in a Workbook. It worked in the October File But when I renamed the file to November and changed some of the column reference in VBA, the command button doesn't work.

    Thanks,

    Sub Month_Updates()
    '
    ' Month_Updates Macro
    ' Formatting of boardpack for each month for each department
    '
    ' Keyboard Shortcut: Ctrl+w
    '
        Range("A9:N9").Select
        ActiveCell.FormulaR1C1 = _
            "        CORPORATE FINANCIAL REPORTS TO THE 30th November 2013"
        Range("B43").Select
        ActiveCell.FormulaR1C1 = "8) SOLVENCY STATEMENT AS AT THE 3OTH NOVEMBER 2013"
        ActiveSheet.Next.Select
        Range("K5:K56").Select
        Selection.Copy
        Range("M5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("Q5:Q12,Q18:Q23,Q31:Q35,Q41,Q49:Q54").Select
        Range("Q49").Activate
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("H5:H12,H18:H23,H31:H35,H41,H49:H54").Select
        Range("H49").Activate
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Sheets("Cons Profit & Loss").Select
        Columns("B:N").Select
        Selection.EntireColumn.Hidden = False
        Columns("G:M").Select
        Selection.EntireColumn.Hidden = True
        Columns("E:E").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("N4").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Columns("W:W").Select
        Selection.EntireColumn.Hidden = True
        Range("D9").Select
        Selection.AutoFill Destination:=Range("d9:e9"), Type:=xlFillDefault
        Range("E9:F9").Select
        Range("E9").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Range("D7:G15").Select
        Range("F9").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 13434828
            .TintAndShade = 0
            .PatternTintAndShade = 0
        Range("E41:E43").Select
        Application.CutCopyMode = False
        Selection.AutoFill Destination:=Range("d41:e43"), Type:=xlFillDefault
        Range("E41:F43").Select
        Range("E41:E43").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("F41:F43").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 13434828
            .TintAndShade = 0
            .PatternTintAndShade = 0
        Sheets("Aust Summary").Select
        Range("E8:E13,E16:E19,E23,E27:E30").Select
        Range("E27").Activate
        Selection.Replace What:="H", Replacement:="I", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Sheets("Detail Aust Opers Results").Select
        Columns("E:Q").Select
        Selection.EntireColumn.Hidden = False
        Columns("J:P").Select
        Selection.EntireColumn.Hidden = True
        Columns("H:H").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("Q7").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q11").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Columns("Z:Z").Select
        Selection.EntireColumn.Hidden = True
        Range("Q17:Q378").Select
        Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("R17:R378").Select
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("R17").Select
        Range("H366").Select
        Selection.AutoFill Destination:=Range("H366:I366"), Type:=xlFillDefault
        Range("H366:I366").Select
        Range("H366").Select
        Selection.Copy
        ActiveSheet.Paste
        Sheets("Detail Aust Dept Results").Select
        Columns("E:Q").Select
        Selection.EntireColumn.Hidden = False
        Columns("J:P").Select
        Selection.EntireColumn.Hidden = True
        Columns("H:H").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Columns("Z:Z").Select
        Selection.EntireColumn.Hidden = True
        Range("Q6").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q10").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        ActiveSheet.Next.Select
        ActiveSheet.Next.Select
        Columns("E:Q").Select
        Selection.EntireColumn.Hidden = False
        Columns("J:P").Select
        Selection.EntireColumn.Hidden = True
        Columns("H:H").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("Q8").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q12").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Columns("Z:Z").Select
        Selection.EntireColumn.Hidden = True
        Range("Q17:Q175").Select
        Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("R17:R177").Select
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        ActiveSheet.Next.Select
        Columns("E:Q").Select
        Selection.EntireColumn.Hidden = False
        Range("I17:I161").Select
        Selection.Copy
        Range("I17").Select
        ActiveSheet.Paste
        Range("I17:I161").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("H17").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("J:P").Select
        Selection.EntireColumn.Hidden = True
        Columns("AH:AK").Select
        Selection.EntireColumn.Hidden = False
        Range("AJ17:AJ161").Select
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Columns("H:H").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("Q8").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q11").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Columns("Z:Z").Select
        Selection.EntireColumn.Hidden = True
        Range("Q16:Q160").Select
        Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("R16:R160").Select
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        ActiveSheet.Next.Select
        ActiveSheet.Next.Select
        ActiveSheet.Next.Select
        ActiveSheet.Next.Select
        Columns("C:O").Select
        Selection.EntireColumn.Hidden = False
        Range("F15:F161").Select
        Selection.Copy
        Range("G15").Select
        ActiveSheet.Paste
        Range("F15:F161").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("F15").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("H:N").Select
        Selection.EntireColumn.Hidden = True
        Columns("F:F").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("O7").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Columns("X:X").Select
        Selection.EntireColumn.Hidden = True
        Range("O15:O139").Select
        Selection.Replace What:="X", Replacement:="Y", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("P15:P139").Select
        Selection.Replace What:="F", Replacement:="G", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("Q15").Select
        ActiveSheet.Next.Select
        Columns("E:Q").Select
        Selection.EntireColumn.Hidden = False
        Columns("J:P").Select
        Selection.EntireColumn.Hidden = True
        Columns("H:H").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("Q8").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q11").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Columns("Z:Z").Select
        Selection.EntireColumn.Hidden = True
        Range("Q15:Q115").Select
        Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("R15:R115").Select
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        ActiveSheet.Next.Select
        Columns("E:Q").Select
        Selection.EntireColumn.Hidden = False
        Columns("J:P").Select
        Selection.EntireColumn.Hidden = True
        Columns("H:H").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("Q8").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q11").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Columns("Z:Z").Select
        Selection.EntireColumn.Hidden = True
        Range("Q15:Q115").Select
        Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("R15:R115").Select
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        ActiveSheet.Next.Select
        Columns("E:Q").Select
        Selection.EntireColumn.Hidden = False
        Columns("J:P").Select
        Selection.EntireColumn.Hidden = True
        Columns("H:H").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Columns("Z:Z").Select
        Selection.EntireColumn.Hidden = True
        Range("Q7").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q10").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q16:Q141").Select
        Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("R16:R141").Select
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        ActiveSheet.Next.Select
        Columns("E:Q").Select
        Selection.EntireColumn.Hidden = False
        Columns("J:P").Select
        Selection.EntireColumn.Hidden = True
        Columns("H:H").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Columns("Z:Z").Select
        Selection.EntireColumn.Hidden = True
        Range("Q8").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q11").Select
        ActiveCell.FormulaR1C1 = "=RC[-8]"
        Range("Q16:Q141").Select
        Selection.Replace What:="Y", Replacement:="Z", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("R16:R141").Select
        Selection.Replace What:="I", Replacement:="J", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=False
    Last edited by Fletch74; 12-05-2013 at 08:57 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Disable command button not working in excel 2010
    By cneha1991 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-30-2011, 07:36 AM
  2. Command button code not working
    By mariposa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2011, 05:03 AM
  3. [SOLVED] Command button is not working by using VBA
    By Webtekr in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2009, 02:22 AM
  4. Command button not working properly on different sheet
    By Strikez in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2007, 12:36 AM
  5. command button isn't working
    By Nydia in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-29-2005, 06:06 PM

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