Help with Excel VBA not saving print settings on second worksheet

[lilp32]

I have an excel program that is set to create a workbook, import data into 2 worksheets and reformat the sheets for printing. I need to format the print settings for both worksheets but the print settings on the second worksheet are not being saved. The program works fine when I go step by step but when I close the workbook and open it back up, the second worksheet has lost its print settings. I am including the entire program but step 17 is what is being lost. I am sure it must be something simple but am at a loss. If I set the workbook to save but not close (stop after ActiveWorkbook. Save) and then close the workbook manually it does save the settings, but I need this to run automatically.

Option Explicit
Sub Reformat()
'
' List Macro
'

'3-4 Prompt for date and open today's file
Dim DateToday As String
Dim NewBook As Workbook

DateToday = Format((Now), "mm-dd-yy")

'1. Create the workbook
Set NewBook = Workbooks.Add(1)
    NewBook.SaveAs Filename:="C:\workbook_" & DateToday & ".xls", FileFormat:=56
    Workbooks.Open Filename:="C:\workbook_" & DateToday & ".xls"
 
'2. Import the Sheet 1 data
    ActiveWorkbook.ActiveSheet.Name = "sheet1" & DateToday
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;link" _
        , Destination:=Range("$A$1"))
        .Name = "sheet1" & Now()
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
'3. Delete data connections
    ActiveSheet.QueryTables(1).Delete

'4. Add Cultures column and sort by Last in Lab (Descending)
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells.Sort Key1:=Range("X1"), Order1:=xlDescending, Header:=xlYes
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Cultures"

'5. Format first row and rename fields
    Rows("1:1").Font.Bold = True
    Rows("1:1").HorizontalAlignment = xlCenter
    Rows("1:1").VerticalAlignment = xlBottom
       
'6. Format date columns
    Range("F:F,K:K,W:W,X:X").Select
    Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
     
'7. Autofit columns
    Cells.EntireColumn.AutoFit

'8. Hide unnecessary columns
    Range("E:E,J:J,M:M,O:V").EntireColumn.Hidden = True
    
'9. Format for printing
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .PrintGridlines = True
        .CenterHorizontally = True
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .CenterFooter = "&""Century Schoolbook,Regular""&20&A"
    End With
    
    Range("A1").Select

'10. Import the Sheet 2 data
        ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "sheet2" & DateToday
        With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;link2" _
        , Destination:=Range("$A$1"))
        .Name = "sheet2" & Now()
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
'11. Delete data connections
    ActiveSheet.QueryTables(1).Delete

'12. Add Cultures column and sort by Last in Lab (Descending)
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells.Sort Key1:=Range("X1"), Order1:=xlDescending, Header:=xlYes
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Cultures"

'13. Format first row and rename fields
    Rows("1:1").Font.Bold = True
    Rows("1:1").HorizontalAlignment = xlCenter
    Rows("1:1").VerticalAlignment = xlBottom
   
'14. Format date columns
    Range("F:F,K:K,W:W,X:X").Select
    Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
     
'15. Autofit columns
    Cells.EntireColumn.AutoFit

'16. Hide unnecessary columns
    Range("E:E,J:J,M:M,O:V").EntireColumn.Hidden = True

'17. Format for printing
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .PrintGridlines = True
        .CenterHorizontally = True
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .CenterFooter = "&""Century Schoolbook,Regular""&20&A"
    End With
    
Range("A1").Select

'18. Go back to first worksheet, close and save

Sheets(1).Activate

ActiveWorkbook.Save
ActiveWorkbook.Close True

End Sub

[NeoPa]

It's such a shame that, after posting such a clear description of your problem, I'm unable to give much help.

I've checked your code (which is also nice and clear with decent comments) but I see nothing there that would account for the behaviour you describe. This doesn't surprise me, as what you describe sounds like the sort of error that is within the application (Excel), rather than the code. I can't imagine they would design a code interface to request that settings for a specific (or even any non-active) worksheet be ignored when saving the workbook. If that's happening it sounds like a problem with Excel itself - rather than your code.

You could try the DoEvents statement after step #17. You could alternatively try doing a Save on the Workbook before returning to the first Worksheet, then again afterwards. Kludgy, but if it works, then better than not I expect.

Let us know how you get on with these ideas.

[lilp32]

Thank you for the suggestions. I tried both and neither was successful. It is frustrating because it works fine if I go step by step.

[NeoPa]

Such a shame. Sorry nothing worked for you.

As a matter of interest, what are the versions you have for Windows and Excel?

[lilp32]

I am using Windows 7 and Excel 2010.

[zmbd]

Using the line numbers in the posted code block:

Line 158
Save the workbook BEFORE switching worksheets.
Resave the workbook again once you have switched.

Personally, I would also do a save at line 87 before you import the next worksheet.

Little known bug in Excel that will crop up without rhyme-nor-reason; however, I've ran into the same event once in awhile when formating in VBA for print and that was the only thing that seemed to work.

[NeoPa]

That seems very much like what the OP reported they tried and failed in post #3 Z, but yours does have one extra save point in it. It's probably worth trying again as nothing else has worked so far :-(

[lilp32]

I've added save points in multiple places including Line 87, 158, etc. and still no success.

[Rabbit]

I am using Windows 7 and Excel 2007 and the code works fine for me. I had to comment out the data import steps and the PrintCommunicat ion steps but other than that, the print settings stuck.

[NeoPa]

I'm glad you posted that Rabbit, because I must admit I would expect them to. This must be something specific to the OP's setup or workbook file. The logic suggested is perfectly sound I'm sure.

[lilp32]

Interesting, when I commented out the print communication steps I lost the "fit to one page" settings on both sheets but maintained the other settings.

[Rabbit]

In that case, try moving the print communication steps to before and after the fit to page lines.

P.S.
From what I could find out about the print communication method, it's only to speed up the process of applying the settings. Try leaving it turned on the entire time.

[and2k]

Hi. I know this is old but curious if you were able to resolve this. I'm having the same exact issue! Very frustrating

[NeoPa]

That's fine, as a comment. Please understand though, that should you require help you will be obliged to create a new thread in which to ask any questions. It's perfectly acceptable to include a link to this, or other, threads in yours of course.

[and2k]

Ok I'll create a separate thread referring to this one. Thank you.