Why is Sheet deleted when Macro runs...

[Darin Kramer]

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"

 

[Guest]

this line

Worksheets("Macros").Visible = xlVeryHidden


hides the sheet, but doesn't delete it. There are no commands in your code
that delete sheets.

Maybe at the end of your macro put in
thisworkbook.Worksheets("Macros").Visible = xlSheetVisible

 

[Darin Kramer]

I agree, which is why I am so confused.

If I try add VBA to unhide it (like u suggested) - Macro stops running
at that point with an error (because it cant find the sheet to unhide)

If I dont hide it to start with, then Macro overides the contents of the
Macro sheet with the paste from the various workbooks.

All Im trying to achieve is to have a standard macro sheet visible at
the beginning and end of the process.

Any alternative suggestions?

Appreciate your asssitance!!!

D

 

[Guest]

Remove the line that hides the sheet.

Put in a line that activates the sheet you want to be the activesheet.

go to Tools=>Protect=>Protect Workbook and protect the structure of the
workbook.

Then run the code. If something tries to delete the macro sheet, then you
should get an error message. Hit debug and see what line of code is
highlighted.