+ Reply to Thread
Results 1 to 14 of 14

Delete middle (same)worksheets in many workbooks

  1. #1
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Delete middle (same)worksheets in many workbooks

    Hi,


    In the attachments is the original format of the workbook, but the sheets are blanco. Did this on purpose, because the info is sensitive.

    Is there a method to delete the middle sheets, 'Totaal1999' and 'Klad1'?

    Is there a VBA code do this in a lot of workbooks, the format of all these books are the same.

    Plus i'll place them in one map if it's necessary.

    See attachment.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Delete middle (same)worksheets in many workbooks

    Total1999 and Klad1 are not middle sheets, they are sheets at the end. Are you simply wanting to delete those two specific sheets by name from all workbooks in a given folder?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Re: Delete middle (same)worksheets in many workbooks

    Yes. That specific names, at 3/4 or something.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Delete middle (same)worksheets in many workbooks

    What do you mean at 3/4 or something? Did you simply mean 'yes, how to delete Total1999 and Klad1 from all workbooks in a folder'?

  5. #5
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Re: Delete middle (same)worksheets in many workbooks

    That's exactly it.

    I want to delete worksheets Total1999 and Klad1 from all workbooks in a folder. Yes.
    Last edited by JBeaucaire; 05-08-2014 at 05:40 PM. Reason: correction in statement, underlined

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Delete middle (same)worksheets in many workbooks

    Maybe this will do it:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Re: Delete middle (same)worksheets in many workbooks

    Do i have to change something?

    I don't use scripts a lot.

    Because it doesn't work.

    Something to do with the green text?

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Delete middle (same)worksheets in many workbooks

    You should read each line of any macro you're ever going use several times until the logic of what that line does starts to come clear. VBA is not rocket science, anyone who takes the time to read the code, read it again, read the comments in green, it should become clear.

    Yes, once you're fully familiar with the code, you will want to edit the fPATH to your actual filepath, the folder where the files to process will be find. Again, reading this code multiple times should make this stuff jump out at you, even if it's unfamiliar.

    Then you will put this code into a standard code module (Insert > Module from inside the VBEditor) and then run.

  9. #9
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Re: Delete middle (same)worksheets in many workbooks

    I will try.

    Just one question about the path name, should i do it in dutch? Or would it recognize the english name, because the 'location (directory)' is the same..

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Delete middle (same)worksheets in many workbooks

    Only you know the proper way to fill out the path, it would be the same as you seen when you look at folder paths in Windows Explorer.

  11. #11
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Re: Delete middle (same)worksheets in many workbooks

    How do you come up at these lines of code?

    I know, what looping is etc.

    But still some codes, do you use a manual or something?

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Delete middle (same)worksheets in many workbooks

    No, I've been working on the forum here for years and everything I've learned has come using the builtin macro recorder to get basic ideas, then editing that code down for efficiency and discussing this with others here on the forum. Each macro I've written has taught me more and more until now, like with anything, I have a bag of tricks I use for basic code writing.

    Occasionally someone will post a question that forces me to Google and research and learn something new to be able to help them, so I learn by teaching.

  13. #13
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Re: Delete middle (same)worksheets in many workbooks

    What am i missing?
    It doesn't work.




    Please Login or Register  to view this content.

  14. #14
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Delete middle (same)worksheets in many workbooks

    Assuming your path is correct.
    I notice you edited the fNAME filter to "*.xlsx*" which should be fine, though what I had in there originally was better.

    My only guess is that it isn't finding any files to open.

    In the VBEditor put an apostrophe at the start of the Application.ScreenUpdating=False so it looks like this:
    Please Login or Register  to view this content.
    That turns off that line of code for now by turning it into a comment.

    Now press F8 to start to run the macro line by line. As you keep pressing F8 slowly the next line of code will execute. When you pass the fNAME = line of code that gets the first filename, stop and hover your mouse over the word fNAME. Does a filename popup for you to read?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Delete text in middle of field
    By Beckl46 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-26-2013, 09:32 AM
  2. [SOLVED] Excel 2003 Formula or Edit/Replace to delete middle initials not working
    By cajodonn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-27-2012, 11:04 AM
  3. Replies: 0
    Last Post: 07-26-2012, 03:54 PM
  4. delete middle part of text string
    By maacmaac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2009, 03:59 PM
  5. Need to delete 2 characters from the middle of a cell.
    By catfish252 in forum Excel General
    Replies: 0
    Last Post: 04-04-2007, 02:38 PM

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