+ Reply to Thread
Results 1 to 4 of 4

Why is Sheet deleted when Macro runs...

  1. #1
    Darin Kramer
    Guest

    Why is Sheet deleted when Macro runs...

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

  2. #2
    Tom Ogilvy
    Guest

    RE: Why is Sheet deleted when Macro runs...

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


  3. #3
    Darin Kramer
    Guest

    RE: Why is Sheet deleted when Macro runs...


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

  4. #4
    Tom Ogilvy
    Guest

    RE: Why is Sheet deleted when Macro runs...

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1