only works on second pass

[board]

the code below should copy specified cells from open workbook and paste them into a new workbook and save it with
yesterdays date. On first pass it opens a new workbook that doesnt have anything in it. On second pass it works. Any ideas?

Option Explicit
Private Sub CopytoNewWorkbook()
Dim myWB As Workbook
Dim tempWB As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo err

Set myWB = ThisWorkbook
Workbooks("Tonnage chart.xlsm").Worksheets("Average").Range("A29:E50").Copy

Set tempWB = Application.Workbooks.Add(1)
With tempWB
.Sheets(1).Range("A1").PasteSpecial xlPasteAll
.Sheets(1).Range("A1").PasteSpecial xlPasteColumnWidths
Range("A51").Select
.SaveAs Filename:="C:\Users\Wilsons PC\Desktop\New Folder" & "Major Stops " & Format(DateAdd("d", -1, Date), "ddd, dd-mm-yyyy")
.Close
End With
err:
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Cheers Stuart

 

[JoeMo]

Does anything happen on first pass other than a new workbook opening?

 

[board]

No, I tried right clicking to paste to see if its copied the values but there is nothing to paste. First time I run manually I get nothing. Second time I run the macro it works. If however I shut the empty sheet before the second run I get nothing again. When the empty sheet is open it works second time round.

 

[JoeMo]

No, I tried right clicking to paste to see if its copied the values but there is nothing to paste. First time I run manually I get nothing. Second time I run the macro it works. If however I shut the empty sheet before the second run I get nothing again. When the empty sheet is open it works second time round.

I've stepped through your code up to the SaveAs line and it runs fine for me so I can't diagnose your problem. Have you tried stepping through the code using the F8 key to see what happens after the new workbook is opened?

 

[board]

Stepped through and when it gets to the new workbook. It goes to this code, which obviously I didn't think about. I assume this now disables what I now want it to do.

Private Sub Workbook_Deactivate()
'clear ribbons
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
're-enable drag and drop
Application.CellDragAndDrop = True
'below is bar with screen size
Application.DisplayStatusBar = Not Application.DisplayStatusBar

'ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True

End Sub

Thanks for the F8 advice, I should have known better.

Stuart

 

[board]

removed the above and now it works. Will have to look into the consequence of removing above code.

Many thanks
Stuart

 

[JoeMo]

Stepped through and when it gets to the new workbook. It goes to this code, which obviously I didn't think about. I assume this now disables what I now want it to do.

Private Sub Workbook_Deactivate()
'clear ribbons
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
're-enable drag and drop
Application.CellDragAndDrop = True
'below is bar with screen size
Application.DisplayStatusBar = Not Application.DisplayStatusBar

'ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True

End Sub

Thanks for the F8 advice, I should have known better.

Stuart

You didn't say you have event code running in your workbook. In this case, when you open a new workbook, you automatically deactivate the primary workbook which triggers the event code.