If they stored all the files in one folder, then as soon as one of those
workbooks is open, then you can use the folder that holds that open file.

It kind of looks like "Conner Time Compliance.xls" is the workbook that owns the
code. If that's true, then you can use ThisWorkbook instead of using a
variable.


Option Explicit
Sub testme()

Dim CallSummWkbk As Workbook
Dim RepPerfWkbk As Workbook
Dim ConnerTimeWkbk As Workbook 'may not be needed???

Set CallSummWkbk = Workbooks.Open _
(Filename:=ThisWorkbook.Path & "\" & "Call Summary.xls")

Set RepPerfWkbk = Workbooks.Open _
(filenameThisWorkbook.Path & "\" & "Rep Performance Analysis.xls")

'do you need conner time compliance to be opened?
Set ConnerTimeWkbk = Workbooks.Open _
(filenameThisWorkbook.Path & "\" & "Conner Time Compliance.xls")
'or is it the workbook that owns the code that's running?
'so we could just use ThisWorkbook

'which one should be used?
CallSummWkbk.Worksheets("call summary").Range("a1:t4000").Copy _
Destination:=ConnerTimeWkbk.Worksheets("call summary").Range("a1")
'or
CallSummWkbk.Worksheets("call summary").Range("a1:t4000").Copy _
Destination:=ThisWorkbook.Worksheets("call summary").Range("a1")

'same question here...
RepPerfWkbk.Worksheets("rep performance analaysis").Range("a1:T4000").Copy _
Destination:=ConnerTimeWkbk _
.Worksheets("rep performance analaysis").Range("a1")
'or
RepPerfWkbk.Worksheets("rep performance analaysis").Range("a1:T4000").Copy _
Destination:=ThisWorkbook _
.Worksheets("rep performance analaysis").Range("a1")

CallSummWkbk.Close savechanges:=False
RepPerfWkbk.Close savechanges:=False

End Sub

=======
I don't think you can force anyone to anything you want them to do (well, it
never works 100% for me!).

But I'd tell, er, ask the users to store all the files in a dedictated
folder--just give it a nice unique name:

c:\SummaryAnalysisCompliance

Then you could hardcode the folder using that name. You could use dir() to test
to see if the files exist and give them a message if they don't.

Dim testStr as string

teststr = ""
on error resume next
teststr = dir("c:\SummaryAnalysisCompliance\Call Summary.xls"
on error goto 0

if teststr = "" then
msgbox "input file missing." & _
"And give them a nice message about what they should do"
exit sub
end if

======
If you can't win that, you could always use:
application.getopenfilename to let them point to the file of their choice.

=====
I think I'd try to get them all to use that dedicated folder. It'll make
helping them (over the phone??) getting things set up. And by making it
consistent, users could help each other.

" wrote:

Dave,

In the workbook I have 3 sheets (call summary, rep performance, PTO)
that I reference back and place in the summary sheet, the 4th sheet (
AND the Main Reporting sheet). That was the difference in regards to
your ps.
However; with that being said, you have given me enough information
that I was able to change the subroutines and make them work in each
case. At this point, I have not finished all the conversions from
formulas in the cells to "VBA based formulas" and have reduced the file
size from 6-7 megs to 966 KB which is one of the things I wanted to
achieve, easier to email. The other was previously I had to copy the
workbook into another and paste just the values, which was a pain to
do! And then email....

I believe that it is all a good thing at this point...

The last thing I would like to do is be able to allow the field
managers to utilize this workbook on their own...
But, the opening lines in my code is:

Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\My Documents\ISS RAW
DATA\Time\Call Summary.xls"
Windows("Call Summary.xls").Activate
Workbooks("Call Summary.xls").Sheets("Call
Summary").Range("A1:T4000").Copy _
Workbooks("Conner Time Compliance.xls").Sheets("Call
Summary").Range("A1")
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\My Documents\ISS RAW
DATA\Time\Rep Performance Analysis.xls"
Windows("Rep Performance Analysis.xls").Activate
Workbooks("Rep Performance Analysis.xls").Sheets("Rep
Performance Analysis").Range("a1:T4000").Copy _
Workbooks("Conner Time Compliance.xls").Sheets("Rep Performance
Analysis").Range("A1")
Workbooks("Call Summary.xls").Close
Workbooks("Rep Performance Analysis.xls").Close

What I would like is for the field managers to be able to save this
workbook in a specified folder along with "Call Summary" and "Rep
Performance Analysis" then be able to run this...
My question now is, How would I write it so that the "Summary Sheet"
knows which folder to in for the other sheets? i.e. what if a manager
saves these files in c:\My Documents\My Reports.
The code would not read,"Workbooks.Open Filename:=
c:\MyDocuments\MyReports.... So how would I make this happen?

And Thanks for the explanation earlier...that helped ALOT in
understanding how things happen!

Hans

--

Dave Peterson