Results 1 to 7 of 7
  1. #1
    A.Rubin
    Guest

    Removing Excel Sheet Macros (Excel 97 SR-2)

    (How DO you tell the VB version? -- Help/About returns no version info)

    I have a Excel 97 worksheet with sheet macros. How can I copy just the sheet, without the macros, to another workbook, from within a macro.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Removing Excel Sheet Macros (Excel 97 SR-2)

    A.Rubin

    This line will copy sheet1 into another workbook:

    Sheets("Sheet1").Copy

    Now about the version, Help|About should work. It will say something to the effect of:

    Microsoft Excel 97 SR-2.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Rob Bruce
    Guest

    Re: Removing Excel Sheet Macros (Excel 97 SR-2)

    I think the version thing was to do with the VB Editor rather than Excel. VBA in Office 97 doesn't give you a version number in the about box , but typing:
    ?application.VBE.Version
    in the immediate window and hitting enter will return the version number in all versions of Office from 97 onwards. I get 5.00 for Office 97.

  4. #4
    A.Rubin
    Guest

    Re: Removing Excel Sheet Macros (Excel 97 SR-2)

    The copying isn't the problem. The code I use for that is:
    <hr>ThisWorkbook.Worksheets(1).Copy After:=there.Worksheets(there.Worksheets.Count)<hr >
    where <font face="Georgia">there</font face=georgia> is an open workbook. The problem is to delete the sheet macros from <font face="Georgia">there</font face=georgia>. Code to delete all macros from a workbook would be acceptable, as there are not supposed to be any macros <font face="Georgia">there</font face=georgia>.

    Yes, the VB version is 5.00.

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Removing Excel Sheet Macros (Excel 97 SR-2)

    I don't know if you can. When you copy a worksheet to another workbook, you are copying an object with all of its properties. Having the code is one property of the worksheet object, so you can't exclude it.

    I suggest that you delete the code after you have copied the worksheet, or remark the code and then copy it. In any case you have to do more work.

    Possibly you can have a dummy worksheet that looks just like the one you want to copy without the code. And copy this one vs the other with code.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    A.Rubin
    Guest

    Re: Removing Excel Sheet Macros (Excel 97 SR-2)

    <P ID="del"><FONT SIZE=-1>Post deleted by gwhitfield</FONT>

  7. #7
    A.Rubin
    Guest

    Re: Removing Excel Sheet Macros (Excel 97 SR-2)

    Never mind, I got it:
    <hr>ThisWorkbook.Worksheets(1).Copy After:=there.Worksheets(there.Worksheets.Count)
    With there.VBProject.VBComponents(ActiveSheet.CodeName) .CodeModule
    .DeleteLines 1, .CountOfLines
    End With
    <hr>

Posting Permissions

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