Copying 3 worksheets to a new workbook

[jimmy2times]

Hello,

I have the following code which is 99% working as intended

Sub ThreeSheets()
'Turn off screen updating
Application.ScreenUpdating = False


'Make the hidden worksheets unhidden so I can copy them
Worksheets("April Performance").Visible = True
Worksheets("May Performance").Visible = True


'Open With Structure to set page layout
With Worksheets("June Performance").PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.PaperSize = xlPaperA3
.Orientation = xlLandscape
End With


'Open a With structure
'Copy the worksheets listed in the array into a newly created workbook
'Put them before sheet 1
'Hide sheet 1
With ActiveWorkbook.Sheets(Array("April Performance", "May Performance", "June Performance"))
.Copy _
before:=Workbooks.Add.Worksheets(1)
ActiveWorkbook.Sheets("Sheet1").Visible = xlSheetVeryHidden
End With


'Activate the existing workbook and hide the tabs I want to be hidden
ThisWorkbook.Activate
Worksheets("April Performance").Visible = False
Worksheets("May Performance").Visible = False


'Turn on screen updating
Application.ScreenUpdating = True
End Sub

The only issue is that April and May performance contain formulas and I want to paste special values for these worksheets in the new workbook. I add in the following code before turning the screen update back on
With ActiveWorkbook.Sheets(Array("April Performance", "May Performance"))
.Cells.PasteSpecial xlValues
.Cells.PasteSpecial xlFormats
End With

This is giving me a run time error "Object doesn't support this property or method"

Can anyone help me with the last piece of code so I can paste special values for the worksheets specified in the new workbook.

Many Thanks

 

[JackDanIce]

Try:

Sub Three_Sheets()

    Dim wkb     As Workbook
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Sheets("April Performance").Visible = True
    Sheets("May Performance").Visible = True

    With Sheets("June Performance")
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
        .PaperSize = xlPaperA3
        .Orientation = xlLandscape
    End With
    
    With Sheets(Array("April Performance", "May Performance", "June Performance"))
        .Copy before:=Workbooks.add.Sheets(1)
        .Sheets("Sheet1").Delete
    End With
    Set wkb = ActiveWorkbook
    
    With wkb
        .Sheets("April performance").Cells.Value = .Sheets("April performance").Cells.Value
        .Sheets("May performance").Cells.Value = .Sheets("May performance").Cells.Value
    End With
    
    With ThisWorkbook
        .Sheets("April Performance").Visible = False
        .Sheets("May Performance").Visible = False
        .Activate
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
    Set wkb = Nothing
    
End Sub

 

[jimmy2times]

Thanks for trying but still copying the formulas not the values.

I have the following code which is 99% working as intended

Sub ThreeSheets()
'Turn off screen updating
Application.ScreenUpdating = False


'Make the hidden worksheets unhidden so I can copy them
Worksheets("April Performance").Visible = True
Worksheets("May Performance").Visible = True


'Open With Structure to set page layout
With Worksheets("June Performance").PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.PaperSize = xlPaperA3
.Orientation = xlLandscape
End With


'Open a With structure
'Copy the worksheets listed in the array into a newly created workbook
'Put them before sheet 1
'Hide sheet 1
With ActiveWorkbook.Sheets(Array("April Performance", "May Performance", "June Performance"))
.Copy _
before:=Workbooks.Add.Worksheets(1)
ActiveWorkbook.Sheets("Sheet1").Visible = xlSheetVeryHidden
End With


'Activate the existing workbook and hide the tabs I want to be hidden
ThisWorkbook.Activate
Worksheets("April Performance").Visible = False
Worksheets("May Performance").Visible = False


'Turn on screen updating
Application.ScreenUpdating = True
End Sub

The only issue is that April and May performance contain formulas and I want to paste special values for these worksheets in the new workbook. I add in the following code before turning the screen update back on
With ActiveWorkbook.Sheets(Array("April Performance", "May Performance"))
.Cells.PasteSpecial xlValues
.Cells.PasteSpecial xlFormats
End With

This is giving me a run time error "Object doesn't support this property or method"

Can anyone help me with the last piece of code so I can paste special values for the worksheets specified in the new workbook.

Many Thanks[/QUOTE]

 

[JackDanIce]

Just tested this and it works in creating a new workbook with 3 sheets and all formula values are hardcoded:

Sub Three_Sheets()
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Sheets("April Performance").Visible = True
    Sheets("May Performance").Visible = True

    With Sheets("June Performance")
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
        .PaperSize = xlPaperA3
        .Orientation = xlLandscape
    End With
    
    Sheets(Array("April Performance", "May Performance", "June Performance")).Copy before:=Workbooks.add.Sheets(1)
    
    With ActiveWorkbook
        .Sheets("April performance").UsedRange.Value = .Sheets("April performance").UsedRange.Value
        .Sheets("May performance").UsedRange.Value = .Sheets("May performance").UsedRange.Value
    End With
    
    With ThisWorkbook
        .Sheets("April Performance").Visible = False
        .Sheets("May Performance").Visible = False
        .Activate
    End With
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub

 

[jimmy2times]

Cheers Jack this seems to work

Sub Three_Sheets()

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

Sheets("April Performance").Visible = True
Sheets("May Performance").Visible = True

With Sheets("June Performance")
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.PaperSize = xlPaperA3
.Orientation = xlLandscape
End With

Sheets(Array("April Performance", "May Performance", "June Performance")).Copy before:=Workbooks.add.Sheets(1)

With ActiveWorkbook
.Sheets("April performance").UsedRange.Value = .Sheets("April performance").UsedRange.Value
.Sheets("May performance").UsedRange.Value = .Sheets("May performance").UsedRange.Value
End With

With ThisWorkbook
.Sheets("April Performance").Visible = False
.Sheets("May Performance").Visible = False
.Activate
End With

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub[/code][/QUOTE]

 

[JackDanIce]

You're welcome