+ Reply to Thread
Results 1 to 6 of 6

Building Error Checks into my Worksheet

  1. #1
    Registered User
    Join Date
    10-17-2017
    Location
    chciago
    MS-Off Ver
    2013
    Posts
    29

    Building Error Checks into my Worksheet

    Hello,

    I am working on building the following error checks into my worksheet via VBA and wondering if anyone could provide me with some simple and efficient code. My worksheet has about 40 detail tabs and a single summary tab and a single error check tab.

    1. The first error check I would like to do, pulls a value from a cell on each detail tab and pastes it on the error check sheet. I need this to paste both the sheet name it is pasting from along with the value. (I am using this to ensure the correct number of values appear on my summary sheet without having to count the number of line items on each tab seperately)

    2. The next check I would like to do simply counts the number of tabs in the workbook if your thier name isnt 'Summary', 'Error Check' or any other tabs I define.

    Thanks in advance. I know this seems like simple fiuunctionaity but I have not been able to get anything to work without significant performance hits.

    SB

  2. #2
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Building Error Checks into my Worksheet

    #2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Not exactly considering excluding specific names, but if you know you have those sheets in the file you just subtract the number of sheets you want to exclude from the count from the total number of sheets in the file.

    #1

    Do the number of sheets in the workbook change (or better asked 'should they change')? You say pulling a value from each detail sheet, is that value always in the same place on each sheet (A1 on each detail sheet)?

    If the sheets are pretty static and the value you are pulling (I am guessing a count of rows) is the same then you can easily create a list of sheet names and then next to each just reference that cell on that sheet to get the value.

    A sample file would help us help you. Any further detail/explanation would enable us to provide more detailed solutions.

    I would avoid VBA with this if possible. So far nothing in your request seems to require VBA, and using VBA will require saving as a macro enabled file, require others to enable macros, and could break over time (making it harder to fix than a formula).
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  3. #3
    Registered User
    Join Date
    10-17-2017
    Location
    chciago
    MS-Off Ver
    2013
    Posts
    29

    Re: Building Error Checks into my Worksheet

    I am copying from AJ15:AK15 on each sheet besides those I defined and I want to paste those values on the 'Error Check' sheet one row after another.

    The reason this will need to be vba is because the sheets names could change or be added/removed and I need a point in time view of this data.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Building Error Checks into my Worksheet

    You could use VBA but you do not need to

    in B2 copy down
    =INDEX(SheetNames,A2)
    in C2 copy down
    =INDIRECT(B2&"!A1")

    Named range "SheetNames" with RefersTo:
    =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

    Produces this
    sheetList.jpg

    You can see that all the sheets are there with #REF - Excel cannot find a 10th sheet

    The named range is an Excel4 macro and so the workbook must be saved as macro enabled
    Attached Files Attached Files
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Building Error Checks into my Worksheet

    VBA alternative
    - test in attached workbook wih {CTRL} k
    - sheet name AND value of cell A1 from each sheet (except excluded sheets) inserted in Sheet "ErrorCheck"

    How it works
    - array of excluded sheet names created
    - all sheets looped looking for match in excuded array
    - if match not found list is updated

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Building Error Checks into my Worksheet

    The next check I would like to do simply counts the number of tabs in the workbook if your thier name isnt 'Summary', 'Error Check' or any other tabs I define.
    Please Login or Register  to view this content.

+ 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. Replies: 10
    Last Post: 07-14-2015, 12:26 AM
  2. Replies: 0
    Last Post: 11-21-2014, 05:00 PM
  3. building a revenue forecasting model and need calendar building help
    By miken33 in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 03-11-2013, 06:53 PM
  4. Replies: 2
    Last Post: 10-08-2012, 12:49 PM
  5. [SOLVED] Formula which checks whether a string is found in a range and checks 2 criteria
    By liranbo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2012, 05:28 PM
  6. IF with OR that checks for #N/A & a 0
    By justywusty in forum Excel General
    Replies: 4
    Last Post: 06-08-2011, 04:31 PM
  7. Error In macro that checks if the file is already open
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-27-2009, 09:21 AM

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