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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,418
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Does anything happen on first pass other than a new workbook opening?
 

board

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,418
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jan 4, 2007
Messages
52

ADVERTISEMENT

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

Board Regular
Joined
Jan 4, 2007
Messages
52
removed the above and now it works. Will have to look into the consequence of removing above code.

Many thanks
Stuart
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,418
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,458
Messages
5,636,383
Members
416,917
Latest member
koto1

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
Top