Altering a Non Active Worksheet

[ShelleyBelly]

Hi All,

I have a macro which, at the moment, creates a worksheet, names it, alters columns etc, adds a table, and converts the table back to a range so that it can later be copied to a new workbook. At the moment all the alterations are made apparent to the user regardless of screenupdating = false.

Is there a way to alter columns and page view, gridlines off, headers off etc whilst not viewing the spreadsheet?

The sheet "interface" would be the starting point and would be visible whilst the sheet "sun & set" is created and altered in the background.

Any suggestions please?

Sub Table_Sunrise() 

Application.ScreenUpdating = False
Application.EnableEvents = False


Dim Tbl As ListObject
Dim wkSheet As Worksheet
Dim wkBook As Workbook


Set wkBook = ActiveWorkbook






PortsKeywords.Calculate
Resize_Sun_Calculations_Tables
Sun.Calculate




Worksheets.Add().Name = "Sun & Set"
Set wkSheet = ActiveSheet


    With wkSheet
        With .PageSetup
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperA4
            .CenterHeader = "&18Sunrise and Sets"
        End With
        With Columns("B:I")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
            .ColumnWidth = 15
        End With
        With Columns("A:A")
            .ColumnWidth = 22
        End With
        With Columns("J:J")
            .ColumnWidth = 1.5
        End With
        With Columns("K:xfd")
            .EntireColumn.Hidden = True
        End With
        With Columns("D:I")
        .ColumnWidth = 7.5
        End With
            With Rows("1:2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .RowHeight = 30
    End With
    With Range("B1:I1")
    .Merge
    .FormulaR1C1 = "Below are the forecast times for sun rise and set if running to scheduled times and speeds  and to the position not necessarily the specified location."
   End With
    End With
    
    
Set Tbl = wkSheet.ListObjects.Add(xlSrcRange, Range("B2:I2"), , xlYes) ' needs changing for an adaptive range ' Adds a table to activesheet, don't delete the source data for the namedrange


Tbl.Resize Tbl.Range.Resize(Range("Date_Range").Value)
    




    Tbl.HeaderRowRange(1) = "Date" 'Title for column 1
    Tbl.HeaderRowRange(2) = "Location"
    Tbl.HeaderRowRange(3) = "Sunrise (UTC)"
    Tbl.HeaderRowRange(4) = "Sunset (UTC)"
    Tbl.HeaderRowRange(5) = "Civil Dawn (LT)"
    Tbl.HeaderRowRange(6) = "Sunrise (LT)"
    Tbl.HeaderRowRange(7) = "Sunset  (LT)"
    Tbl.HeaderRowRange(8) = "Civil Dusk (LT)"
    
    Tbl.DataBodyRange(, 1).NumberFormat = "ddd d mmm yyyy"
    'Tbl.DataBodyRange(, 2).NumberFormat = "@" port name
    Tbl.DataBodyRange(, 3).NumberFormat = "hh:mm"
    Tbl.DataBodyRange(, 4).NumberFormat = "hh:mm"
    Tbl.DataBodyRange(, 5).NumberFormat = "hh:mm"
    Tbl.DataBodyRange(, 6).NumberFormat = "hh:mm"
    Tbl.DataBodyRange(, 7).NumberFormat = "hh:mm"
    Tbl.DataBodyRange(, 8).NumberFormat = "hh:mm"
    
    Tbl.DataBodyRange(, 1) = "='Ports & Keywords'!P2" ' formula for column
    Tbl.DataBodyRange(, 2) = "='Ports & Keywords'!Q2"
    Tbl.DataBodyRange(, 3) = "=Sun!M2"
    Tbl.DataBodyRange(, 4) = "=Sun!Z2"
    Tbl.DataBodyRange(, 5) = "=Sun!AN2"
    Tbl.DataBodyRange(, 6) = "=Sun!N2"
    Tbl.DataBodyRange(, 7) = "=Sun!AA2"
    Tbl.DataBodyRange(, 8) = "=Sun!BA2"
    
    wkSheet.Calculate
    


    With Tbl.DataBodyRange 'with each cell in the databody
     .Value = .Value ' convert to value
    End With


    Tbl.Unlist 'remove table attributes


    With ActiveWindow
        .View = xlPageLayoutView
        .DisplayGridlines = False
        .DisplayHeadings = False
    End With




    Application.ScreenUpdating = True




End Sub

 

[yky]

Don't make "Sun & Set" the activesheet.

Worksheets.Add().Name = "Sun & Set"
Set wkSheet = ActiveSheet
[COLOR=#0000ff]Sheets("interface").Activate[/COLOR]