0
Sub addinfo()

If Range("FH17") = "Unlocked" Then


    Result = MsgBox("Are you sure you want to add information for a new year? (This is only to be done after you have created a new workbook for a new year)", vbYesNo + vbQuestion)

    If Result = vbYes Then
    Range("FH17").Value = "Locked"

    'creating template
    Set wk = ThisWorkbook
    Dim template As String

    template = Sheets("Data").Range("EW12").Value


    'add template
    wk.Sheets("Data").Range("A1:BC1000").Copy wk.Sheets(template).Range("A1")
    'add old info
    wk.Sheets("Data").Range("IE5:IK1000").Copy wk.Sheets(template).Range("A5")
    wk.Sheets("Data").Range("IL5:IM1000").Copy wk.Sheets(template).Range("J5")
    wk.Sheets("Data").Range("IN5:IW1000").Copy wk.Sheets(template).Range("O5")
    'change date
    wk.Sheets("Data").Range("EX12").Copy wk.Sheets(template).Range("E1")


    'copy format to template
    wk.Sheets("Data").Range("A:BC").Copy
    wk.Sheets(template).Range("A1").Parent.Activate
    wk.Sheets(template).Range("A1").PasteSpecial xlPasteColumnWidths
    wk.Sheets(template).Range("A1").PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

    'unhide columns
    wk.Sheets(template).Range("A:BC").Select
    Selection.EntireColumn.Hidden = False

    'Hide columns
    wk.Sheets(template).Range("AC:AD").Select
    Selection.EntireColumn.Hidden = True
    wk.Sheets(template).Range("AM:AN").Select
    Selection.EntireColumn.Hidden = True
    wk.Sheets(template).Range("AR:AT").Select
    Selection.EntireColumn.Hidden = True

    wk.Sheets(template).Range("A1").Select
    Call rows_freeze

    'Protect the sheet
    ActiveSheet.Protect
    MsgBox "Done."
    Else
    Range("FH17").Value = "Locked"

    End If

End If


End Sub

This is my code, it works fine. except the part of calling on "row_freeze". I tried not having it as 2 macros but that didnt work either. I get a 400 error message. Anyone with any idea? The only part that actually needs to be fixed is the code below, I'm trying to autofit row 3 and then freeze everything above cell A4. (I tried to skip the "Rows("3:3").Select" and that did nothing)(the code works if I do it in a an new empty workbook)

Sub rows_freeze()

Rows("3:3").Select
Rows("3:3").EntireRow.AutoFit
Range("A4").Select
ActiveWindow.FreezePanes = True

End Sub

EDIT: I format the width of the columns, I believe that it sometimes "has time" to make the columns big enough for the text to fit. but sometimes it does not and the "wrap text" causes the rows to get very long(height). I removed both lines of code(rows and freeze) and I dont get any error message (this time the rows actually did not need to be autofitted, but sometimes they do).

EDIT 2:

'copy sheet to new workbook
        sheettocopy = Range("EW10").Value
        Worksheets(sheettocopy).Copy


        Dim wb As Workbook

        'Store new workbook into a variable
          Set wb = ActiveWorkbook

        'Fix any macro assigned buttons
          Call FixMacroLinks(wb)



        'adding sheet and renaming
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = Sheets("Data").Range("EW12").Value

        ActiveWorkbook.Sheets("Data").Activate
        MsgBox "Done."
        Else
        MsgBox "You do not have a december month"
        Range("FF17").Value = "Locked"

This is the code when creating the new workbook. The first thing I do when this is done, is to start the "Addinfo" sub

Nick
  • 142
  • 9
  • Off topic but [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) will be an interesting read for you to try avoid methods like `.Select` and references to the `ActiveSheet`. – JvdV Sep 06 '19 at 09:07
  • `Rows("3:3").EntireRow.AutoFit` works for me perfectly. What line does arises the error? – Foxfire And Burns And Burns Sep 06 '19 at 09:16

1 Answers1

0

I have not worked with your sheet before, but I found that I was having the same problem of a 400 error while trying to autofit columns. I was able to resolve the issue when I put my code for autofitting my columns before my code for protecting my cells.

Yunnosch
  • 26,130
  • 9
  • 42
  • 54
Ricky Urban
  • 1
  • Please avoid asking questions in an answer. This actually is an answer, I know. But avoid the misinterpretation that you are asking a question where only answers are accepted. I recommend the phrasing of a conditional answer: "The problem might be that .... In that case the solution is ....". If you don't like that concept and cannot answer without asking, please understand this: https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead – Yunnosch Apr 30 '20 at 06:45