Excel 2003 Macro HELP NEEDED!!

[drmmer07]

Hello fellow excel guru's,

i have just managed to create a macro where once i select the 'button' where i have set the macro up to, there are two data sheets pasted into my workbook from another.

The issue i am facing is that the macro works if the other workbook is open, but as soon as i close the other workbook from where i am getting the data, i get the debugging error.

Can you please tell me why it works when the workbook is open but as soon as its closed the macro wont work?

Here is my macro below

Sub saledata()
'
' saledata Macro
' Macro recorded 20/04/2010 by david.pietersz
'
'
Sheets("Total Sales by Rep").Select
Range("D27").Select
Windows("P:\Shared\CONSUMER DIVISION SALE SCHEDULE\2010 Trackers\Reporting Summary\Sales Schedule Summary - Rep II").Activate
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Home & Garden Sales Schedule - April 2010.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("Sales Schedule Summary - Rep II.xls").Activate
Sheets("Sales Stats").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Home & Garden Sales Schedule - April 2010.xls").Activate
Sheets("Sales Stats").Select
ActiveWindow.SmallScroll Down:=-9
Range("A1").Select
ActiveSheet.Paste
Range("C22").Select
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=-9
End Sub

 

[Smitty]

Welcome to the Board!

Can you please tell me why it works when the workbook is open but as soon as its closed the macro wont work?

Because it's closed and your code is referencing an open workbook. There are methods to pull data from closed workbooks, but in your case, I'd just record a macro opening the source wb and add that to the beginning of your code.

HTH,