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
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