runtime error

[Jonny]

Hello,

I'm currently running a macro which opens a work book(1),
moves all of the sheets from it into another file(2),
which should close the workbook(1) without saving and
keep all of the data in workbook (2).

This works fine when stepping through using F8, and it
checks whether I really do want to close the book without
saving. My problem is that when i automate it, F5, it
comes up with a runtime error.

I've tried using...

Application.DisplayAlerts = False

ActiveWindow.SelectedSheets.Move After:=Workbooks _
("Site Inspection Record.xls").Sheets("program info")

Application.DisplayAlerts = True

.... but this seems to be the thing that causes the error.

Is there anyway to move all of the data without excel
checking anything with me and just doing it nicely.

TIA

 

[Ron de Bruin]

Hi Jonny

Difficult without seeing all your code ?

Try this example that open a file and copy all the worksheets in the activeworkbook

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\test.xls")
Wb2.Worksheets.Copy after:= _
Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Application.ScreenUpdating = True
End Sub

Post back if you need help