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"


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

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy





"Darin Kramer" wrote:

> 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 ***
>

[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




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

[Tom Ogilvy]

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.

--
Regards,
Tom Ogilvy


"Darin Kramer" wrote:

>
> 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
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>