I have been trying to write this macro for quite some time now. I've received
much help already but keep getting stuck. I'm just going to lay it out and
maybe someone knows how the code will work?

I have files for different services, and for every year there is the same
service with different data. Therefore, there are folders for every year, and
in them the different kinds of service. I need to compare the data from two
years for the same service. The problem is, the service files have all the
same name for each year, i.e. 'Service 1' in 2001 and 'Service 1' in 2000. I
want to be able to open up excel and import my macro. I then want it to
prompt for the file name (what service) and what two years it wants to
compare. I know it cant have the same file name open at a time for two
different files, so i want to be able to copy and paste the worksheet into
the excel file i have open, then close and then open the second year's file.

I am having so much trouble getting these files to open. If anyone can help
it would be greatly appreciated.


MSHO

Not sure if this will help, but this is what I do. I open workbooks
created by users and they can name them whatever they want. Since I can
never know what they might name them, I just change the sheet name when
these files get to my workbook.

ActiveSheet.Name = "Whateveryouwanttonameit"

I also use a variable to capture the ActiveWorkbook.Name and use these
to address the incoming workbook.

Bruce


--
Bruce001
------------------------------------------------------------------------
Bruce001's Profile: http://www.excelforum.com/member.php...o&userid=26630
View this thread: http://www.excelforum.com/showthread...hreadid=500637

Hi Bruce001

Try this

Sub Makro1()

Workbooks.Open Filename:= _
"C:\folder2001\Service 1 .xls"
workshhets("????").Activate

'
' copy what you ned
'activate main workboo and sheet to copy to
Workbook("????").Worksheets("????").Activate
' Past
Workbooks("service 1").Close False

'repet this for next workbook to open (change folder name)
End Sub

Regards Yngve