+ Reply to Thread
Results 1 to 16 of 16

Creating Marco for varying number of sheets

  1. #1
    Registered User
    Join Date
    10-15-2016
    Location
    ATLANTA
    MS-Off Ver
    2016
    Posts
    16

    Creating Marco for varying number of sheets

    Hi.

    I have an excel file which contains a few sheets named Sheet1, Sheet2, …., also include AA, BB, CC and DD( As a renamed Sheets). Sheet1, Sheet2, … SheetN, will have data pulled from another source.

    I have created a macro, which formats the data on Sheets1, Sheet2, .......SheetN, "Individually" as per my requirement.

    FYI…”sometimes”, there might be Sheet3, Sheet4, Sheet5 or more sheets of data pulled from another source.

    But my challenge is:

    1) When I click on created Marco button, data on Sheet1, Sheet2, …. SheetN, Etc. should be formatted "At the same time". But the rename sheets AA, BB, CC and DD should not be impacted, because AA, BB, CC and DD contain a different macro.

    2) Also, (like I mentioned in the beginning), user might add several sheets like Sheet3, Sheet4, …SheetN, but AA, BB, CC and DD will remain untouched.

    As a result, the macro that I am looking for should be able to accommodate how many ever sheets (Sheet1, Sheet2, Sheet3, Sheet4….SheetN) the user adds.

    Can someone help/advise me on this?

    Any help is much appreciated.
    Last edited by Roseline; 02-17-2019 at 02:47 PM. Reason: UPDATED

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating Marco for varying number of sheets

    Your problem is easily solved.

    Unfortunately not until you comply with the Forum rules.

    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    10-15-2016
    Location
    ATLANTA
    MS-Off Ver
    2016
    Posts
    16

    Re: Creating Marco for varying number of sheets

    Hi sorry, i didn't realize the rule. I have made the changes to my original post. Please correct me if i am wrong. Thank you.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Creating Marco for varying number of sheets

    I think it will be easier to test possible solutions if you could attach a copy of your file and explain in detail what you want to do using a few examples from your data and referring to specific cells, rows, columns and sheets. De-sensitize the data if necessary.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Registered User
    Join Date
    10-15-2016
    Location
    ATLANTA
    MS-Off Ver
    2016
    Posts
    16

    Re: Creating Marco for varying number of sheets

    Sorry i have updated my original post. The Marco code that i posted earlier, just deletes/formats rows and columns of source data. The only thing i looking for is a macro that will format the data on Sheet1 , Sheet2,...... SheetN, at the "same time", (Right now the data is formatted on each sheet "Individually") without impacting AA, BB, CC, & DD. Also i have tried using the below code, but its impacting AA,BB,CC, & DD sheets, which i do not want.

    Please Login or Register  to view this content.
    Last edited by Roseline; 02-17-2019 at 03:05 PM. Reason: Updated

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating Marco for varying number of sheets

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-15-2016
    Location
    ATLANTA
    MS-Off Ver
    2016
    Posts
    16

    Re: Creating Marco for varying number of sheets

    Thank you so much, it works. But if i have any additional empty sheets, i am receiving an error, which i have attached. Also below is my code. Can you help me resolve it.

    error.JPG
    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating Marco for varying number of sheets

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 02-17-2019 at 06:20 PM.

  9. #9
    Registered User
    Join Date
    10-15-2016
    Location
    ATLANTA
    MS-Off Ver
    2016
    Posts
    16

    Re: Creating Marco for varying number of sheets

    Unfortunately i am receiving the same error.

  10. #10
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating Marco for varying number of sheets

    what do you mean by Empty Sheets?


    Ahhh this part of the code will cause an error if nothing is found:-

    Please Login or Register  to view this content.

    So Try this code:


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 02-17-2019 at 06:44 PM.

  11. #11
    Registered User
    Join Date
    10-15-2016
    Location
    ATLANTA
    MS-Off Ver
    2016
    Posts
    16

    Re: Creating Marco for varying number of sheets

    Unfortunately i am receiving error.But thank you so much, i really appreciate your help.

  12. #12
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating Marco for varying number of sheets

    Which line is the error in?

  13. #13
    Registered User
    Join Date
    10-15-2016
    Location
    ATLANTA
    MS-Off Ver
    2016
    Posts
    16

    Re: Creating Marco for varying number of sheets

    This Line, i am receiving error.


    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating Marco for varying number of sheets

    That is after the line

    Please Login or Register  to view this content.
    So I do not see why you should get an error.

    Are you using this code:

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    10-15-2016
    Location
    ATLANTA
    MS-Off Ver
    2016
    Posts
    16

    Re: Creating Marco for varying number of sheets

    If i use the above code, i am receiving an error, as shown in the attachmenterror.JPG.

    I made some changes to the code, which works on Sheet1,Sheet2,........SheetN, if they have any data in them. But if Sheet6, Sheet7 are empty i am receiving error, which i has mentioned in red font.


    Please Login or Register  to view this content.

  16. #16
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Creating Marco for varying number of sheets

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

+ 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. Creating a Marco to Replace a Specific Number with a Specific Letter in a Column
    By goldbeje in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-27-2011, 11:26 AM
  2. VBA Marco/equations for creating reports
    By Deman_250 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-21-2011, 03:00 AM
  3. Collating data from varying number of sheets
    By munkee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2009, 08:22 PM
  4. Exclamation Collate data from varying number of sheets to one sheet
    By munkee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2009, 06:07 PM
  5. Replies: 0
    Last Post: 11-26-2009, 04:53 PM
  6. Selecting an array with a varying number of sheets
    By cbjorgol in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2008, 09:41 AM
  7. Generic Page Setup Macro applied to multiple workbooks with varying number of sheets
    By sowetoddid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2005, 11:45 AM
  8. VARYING number of sheets in EXCEL
    By Darrin in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-22-2005, 12:06 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