Importing of Worksheets into Master Workbook

[nkkhil]

Hi All,

I was wondering if anyone could help me...

I am using Excel 2003 (Work are a bit slow at moving with the times)

I have a workbook consisting of mutliple worksheets which have some conditional formatting and protected cells. Each worksheet is filled out by various individuals and then used to feed into a summary worksheet.

Due to various network access issues there is now a requirement to send individuals each workseet for then to populate and then design some sort of import routine which imports each worksheet back into the the Master hence feeding into the summary sheet. It could just import the data as the master would retain the conditional formatting

Is this possible through macros?

Anyy help would be greatly appreciated

 

[Trevor G]

Here is some code to open all workbooks in a given folder.

All that has to happen from here is for you to add the code to copy the worksheet into your master workbook.

To do that I suggest you open two workbooks and look to record a macro to copy the sheet to the master workbook, thenmove back to the master workbook that will give you the code you need to add into this routine.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>MyPath = "M:\Access Files\"<br>MyFile = Dir(MyPath)<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.xls" <SPAN style="color:#00007F">Then</SPAN><br>Workbooks.Open MyPath & MyFile<br><SPAN style="color:#007F00">'Whats the sheet name</SPAN><br><SPAN style="color:#007F00">'copy sheet to workbook Extra code goes here</SPAN><br>ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[nkkhil]

Thanks for the prompt response Trevor. I'll give that a try and let you know how i get on

 

[nkkhil]

I have tried to apply the macro but doesn't seem to work?

Also probably need to replace each worksheet otherwise it will break the logic in the summary sheet.

I thought there may be a function to attach the file but can email if it helps...

Sub Open_My_Files()
Dim MyFile As String
MyPath = "C:\Documents and Settings\khilan.dhanani\Desktop\Weekly Report Test\"
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
Windows("Optel T&T.xls").Activate
Sheets("Optel T&T").Select
Sheets("Optel T&T").Copy Before:=Workbooks( _
"18 11 11 weekly report.xls").Sheets(5)
ActiveWorkbook.Close True
End If
MyFile = Dir
Loop
End Sub