+ Reply to Thread
Results 1 to 25 of 25

VBA to Loop through list of sheets and apply macro

  1. #1
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    VBA to Loop through list of sheets and apply macro

    Please help, I've been stuck on this for hours. I have one workbook that is saved as macrobook.xlsm in which column A has a list of worksheet names that I would like to apply a macro to. The worksheets however, are in another workbook called wb05012013.xlsx. The "05012013" refers to a date since the file date is updated and that date value will be in a named cell called "date" in the macrbook.xlsm. I would like the macro to loop through each sheet in wb05012013.xlsx, select all cells and paste values. Then, once the macro comes to a blank cell or end of the list if you will, I'd like it to delete the sheets that are not in the list. Also both of the workbooks are in a shared drive but in the same folder, not sure if that detail makes a difference because the code loops at home, but I get errors at work maybe because the files are stored in shared drives? This is what I have so far...


    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: VBA to Loop through list of sheets and apply macro

    attach please 2 sample files for testing on local drive
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: VBA to Loop through list of sheets and apply macro

    Hi, hungryhobo,

    give this code a try (untested):
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Thanks Holger, your code doesn't loop through the sheets specified in column A. It just stays on the first sheet and doesn't do anything repeatedly.

  5. #5
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    I've attached two sample workbooks. After the macro comes to a stop, I need it to delete all sheets not listed in Column A, which is in the macrobook. Thanks for your help thus far guys.
    Attached Files Attached Files

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: VBA to Loop through list of sheets and apply macro

    Hi, hungryhobo,

    you correct about not looping the sheets in the opened workbook as I misread and wrote code to open several workbooks instead.

    Please give this a try:
    Please Login or Register  to view this content.
    Please consider to use this instead of the loop
    Please Login or Register  to view this content.
    Ciao,
    Holger

  7. #7
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Quote Originally Posted by HaHoBe View Post
    Hi, hungryhobo,

    you correct about not looping the sheets in the opened workbook as I misread and wrote code to open several workbooks instead.


    Holger
    Thanks for the code, how do I get it to delete sheets not in the list once the loop comes to a blank cell or end of list?
    Last edited by hungryhobo; 05-05-2013 at 11:24 PM.

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: VBA to Loop through list of sheets and apply macro

    Hi, hungryhobo,

    please do not quote full posts.

    The end of the list is determined by checking for the last used cell, and I assumed a thoroughly filled area of data.

    You could loop through all sheets in the newly opened workbook and use WorksheetFunction.CountIf on Column A in the worksheet to check whether the sheet as to be altered or deleted. So empty cells would not have any effect on the code, just the sheets in the workbook.

    Ciao,
    Holger

  9. #9
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Thanks for your help thus far. I haven't constructed the code to delete the sheets as im still new to vba. Where in the code would I start the countif macro and how would I close it. I once tried to add in another macro once the loop comes to an end and got errors regarding "for" missing in the vba.

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: VBA to Loop through list of sheets and apply macro

    Hi, hungryhobo,

    instead of looping through the cells to find the sheet names loop through the worksheets of the newly opened workbook like
    Please Login or Register  to view this content.
    Ciao,
    Holger

  11. #11
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    The new code doesn't seemt to loop through the sheets either. This is what I have to test at home.

    Please Login or Register  to view this content.

  12. #12
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: VBA to Loop through list of sheets and apply macro

    Hi, hungryhobo,

    code #4 just left 4 sheets in the newly opened workbook for me when I tested. I donīt understand
    Please Login or Register  to view this content.
    What workbook and sheets are we talking about?

    Ciao,
    Holger

  13. #13
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Aho ok, it does delete the sheets. Now the only issue is my macro doesn't seem to go thorugh each sheet, select all cells, copy and paste as values. When running through the macro with F8, it stays on the same sheet and does nothing but it does delete all the sheets not in the list. This is my code.

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Quote Originally Posted by HaHoBe View Post


    What workbook and sheets are we talking about?
    I'm talk about wb2

  15. #15
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: VBA to Loop through list of sheets and apply macro

    Hi, hungryhobo,

    when recording a macro with the recorder you usually move around in every sheet and switrch sheets.

    Maybe you just have a look at the remaining sheets in the opened workbook - in my copy in every remaining sheet the values in Column A are pasted, thereīs absolutely no need for any cursor movement (although it may thus look as if nothing is happening).

    Ciao,
    Holger

  16. #16
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    for some reason at work, set mywb=activeworkbook dispalys without capital letters and affects the macro with an error when it trys to look for the list of sheet names. Any thoughts?

  17. #17
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    For some reason at work, set mywb=activeworkbook dispalys without capital letters and affects the macro with an error when it trys to look for the list of sheet names. Any thoughts?

  18. #18
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Quote Originally Posted by HaHoBe View Post
    Hi, hungryhobo,

    when recording a macro with the recorder you usually move around in every sheet and switrch sheets.
    for some reason at work, set mywb=activeworkbook dispalys without capital letters and affects the macro with an error when it trys to look for the list of sheet names. Any thoughts?

  19. #19
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Also your code is selecting column A of each sheet, I need it to select all cells in the sheet and paste values. Thanks so much...the code is nearly complete..

  20. #20
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: VBA to Loop through list of sheets and apply macro

    Hi, hungryhobo,

    only suggestion would be to have a look at the References at work where a broken link may be found if itīs a truted location and macros are enabled.

    Sorry I overread that and just had a look at your code which was showing code to work for Column A.

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Ciao,
    Holger

  21. #21
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Thank you so much for your patience Holger! The macro works as intended at home! I'll try to figure out what happens at work.

    Best Regards

  22. #22
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Quote Originally Posted by HaHoBe View Post

    With ws2
    For Each rngCell In .UsedRange
    "If Trim(rngCell.Value) <> vbNullString Then" rngCell.Value = Trim(rngCell.Value) ' The quoted part gives error
    Next rngCell
    End With




    I receive the following error message when testing it at work and all the cells in each sheet still have formulas and haven't been pasted over as values.

    run-time error '13':

    Type mismatch

  23. #23
    Registered User
    Join Date
    06-18-2012
    Location
    united states
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: VBA to Loop through list of sheets and apply macro

    Quote Originally Posted by HaHoBe View Post

    With ws2
    For Each rngCell In .UsedRange
    "If Trim(rngCell.Value) <> vbNullString Then" rngCell.Value = Trim(rngCell.Value) ' The quoted part gives error
    Next rngCell
    End With




    I receive the following error message when testing it at work and all the cells in each sheet still have formulas and haven't been pasted over as values.

    run-time error '13':

    Type mismatch

  24. #24
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: VBA to Loop through list of sheets and apply macro

    Hi, hungryhobo,

    as you may have noticed the code does what itīs supposed to do, and without having a look at your computer at work I think I wonīt be able to determine why the code doesnīt work there.

    Ciao,
    Holger

  25. #25
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to Loop through list of sheets and apply macro


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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