only works on second pass

board

Board Regular
Joined
Jan 4, 2007
Messages
52
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Does anything happen on first pass other than a new workbook opening?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
removed the above and now it works. Will have to look into the consequence of removing above code.

Many thanks
Stuart
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top