Guys

Howdie guys

VBA below works perfectly to consolidate data from the specified
location. HOWEVER to run the macro I inserted a sheet called MACROS
which has the instructions for users and what to click when. Problem is
when I run the macro it deletes the Macros sheet. I tried very hiding
the Macros sheet, but that doesnt seem to work. Any ideas...?

Appreicate the help

Thanks

D

Sub Consolidator()
Worksheets("Macros").Visible = xlVeryHidden

Sheets("Sheet1").Select

Dim i As Long, sName As String, sh As Worksheet
Dim dest As Range, bk As Workbook
i = 1
sName = Dir("D:\Documents and Settings\me\Desktop\results\*.xls")
Do While sName < ""
Set bk = Workbooks.Open("D:\Documents and
Settings\darinkramer\Desktop\Projects\Projects_06\ Consolidation_test\res
ults\" & sName)
Set sh = bk.Worksheets("Answers")
Set dest = ThisWorkbook.Worksheets(1).Cells(1, i)
i = i + 4
sh.Columns(1).Resize(, 4).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName
' close the workbook
bk.Close SaveChanges:=False
sName = Dir()
Loop

ActiveSheet.Name = "Consolidated"


*** Sent via Developersdex http://www.developersdex.com ***