You can call a procedure in a different module in the worksheet_Calculate event.

Karen53 wrote:

Hi Dave,

It doesn't whether the file is open or not. I'll try it with the complete
path.

By the way, does all of the code for a worksheet calculate procedure have to
reside in the worksheet module or can it call out to a general module?
--
Thanks for your help.
Karen53

"Dave Peterson" wrote:

Maybe specifying the complete path to the file that should be opened would help:

Set wbkCopyFrom = Workbooks.Open("test.xls")

would look more like:

Set wbkCopyFrom _
= Workbooks.Open("C:\Documents and Settings\David Peterson\Desktop\test.xls")

for me.

If test.xls is already open, does your code work ok? If no, what line causes
the error?

Karen53 wrote:

Hi,

I have two workbooks in a folder on my desktop. I want to copy from one
workbook to the other workbook. It tells me it cannot locate the file. It
did run once but I have not been able to get it to run again. I have tried
the path various ways with no success. Have I missed something?

Here is my code:

Sub wkbookCreate()

Dim wbkCopyFrom As Workbook
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim wbkName As String

On Error Resume Next

Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else
Application.ScreenUpdating = False

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])

'Pool lists
'CAM
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(Tablespg.Name, _
"'",
"''"))).Range("J4:J21")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(Tablespg.Name, _

"'", "''"))).Range("J4:J21")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])

ThisWorkbook.Sheets((Replace(GrossUpspg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])
'Cam Pools list 2
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(GrossUppg.Name, _
"'",
"''"))).Range("B15:J16")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(GrossUppg.Name, +
"'",
"''"))).Range("B15:J16")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(GrossUppg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])
End If
End If
Done:
Application.ScreenUpdating = True
End Sub

--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson