Replies

Thanks for that!

Works Great!

The sheet exists, as other functions around it work,

I have copied in the full script below:

Code:

Public Pub_worksheet

Sub RSV()
‘
    Pub_worksheet = “Performance report.xls”
    Workbooks.Add
    Windows(Pub_worksheet).Activate
    Application.WindowState = xlMaximized
    Windows.Arrange ArrangeStyle:=xlHorizontal
    
‘   Set up new workbook with sheets

    Windows(“Book1”).Activate
    ActiveWindow.TabRatio = 0.957
    
    Windows(Pub_worksheet).Activate

    Call Copy(“Performance”, 1, 0)
    Call Copy(“Targets”, 0, 0)
    Call Copy(“PATF Daily Mov’t”, 0, 0)
    Call Copy(“PATF Daily Mov’t – Feb 11 – Jun”, 41, 0)
    Call Copy(“PATF Inv”, 0, 1)
    Call Copy(“PATF Reds”, 0, 1)
    Call Copy(“PATF2 Daily Mov’t”, 0, 0)
    Call Copy(“PATF2 Daily Mov’t – Feb 11- Jun”, 41, 0)
    Call Copy(“PATF2 Inv”, 0, 1)
    Call Copy(“PATF2 Reds”, 0, 1)
    Call Copy(“FX”, 0, 0)
    Call Copy(“PATF Inv09-10 (Unknowns)”, 0, 0)
    Call Copy(“PATF2 Inv09-10 (Unknowns)”, 0, 0)
 
    
‘   Delete extra sheets
    Windows(“Book1”).Activate
    Application.DisplayAlerts = False
    Sheets(“Sheet1”).Select
    ActiveWindow.SelectedSheets.Delete

    Application.DisplayAlerts = False
    Sheets(“Sheet2”).Select
    ActiveWindow.SelectedSheets.Delete

    Application.DisplayAlerts = False
    Sheets(“Sheet3”).Select
    ActiveWindow.SelectedSheets.Delete
    
      
‘   Select Performance Report Sheet to finish
    Sheets(“Performance”).Select
    Windows(“Performance report.xls”).Activate
    Sheets(“Performance”).Select
    Range(“A1”).Select

End Sub

Sub Copy(spreadsheet As String, Colour As Integer, Set_Range As Integer)
    Windows(“Book1”).Activate
    Sheets.Add.Name = spreadsheet
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets(spreadsheet).Select
    
    Windows(Pub_worksheet).Activate
    Sheets(spreadsheet).Select
    Cells.Select
    Selection.Copy
    
    Windows(“Book1”).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    If Colour > 0 Then
    ActiveWorkbook.Sheets(spreadsheet).Tab.ColorIndex = Colour
  ‘  ActiveWorkbook.Sheets(spreadsheet).Tab.ColorIndex = Pub_workbook.Sheets(spreadsheet).Tab.ColorIndex
    End If
    
    If Set_Range > 0 Then
    Range(“A3:R3”).Select
    Selection.AutoFilter
    Range(“A5”).Select
    ActiveWindow.FreezePanes = True
    End If
    
    Range(“A1”).Select
    Windows(Pub_worksheet).Activate
    Range(“A1”).Select
End Sub

Its a bit rough round the edges as yet, im working on tidying it up…

Also any ideas on how to create a new enpty workbook rather then having Sheet 1, Sheet 2 and Sheet 3 created for me to delete them later?