+ Reply to Thread
Results 1 to 21 of 21

VBA to Split Workbook: No Longer Converting Formula Results to Values

  1. #1
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    VBA to Split Workbook: No Longer Converting Formula Results to Values

    As you all know, I am not a VBA expert! I am looking for a bit of help again with a macro I got working with your help just under a year ago.

    The macro is supposed to copy a worksheet and, in doing so, convert the formula results in rows 2:4 into values. This bit was working last year, but isn't now.

    I am not trying to create the workbook in a network area.

    The original worksheet looks like this:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    2
    Year Groups &
    Teaching Groups
    Department Staff Initials
    HoD
    3
    HLB
    DKH
    JP
    JWPM
    MRH
    4
    Staff Allocations
    40
    46
    42
    30
    28
    5
    No. of periods
    per fortnight
    Totals
    0
    0
    0
    6
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    Sheet: Art

    and the resulting sheet comes out like this:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    2
    Year Groups &
    Teaching Groups
    Department Staff Initials
    HoD
    3
    #N/A
    4
    Staff Allocations
    5
    No. of periods
    per fortnight
    Totals
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    Sheet: Art

    Not that it should matter, the two formulae used in the original are:

    =LOOKUP(2,1/((Departments[Department]=$H$1)*(Departments[HoD]="*")),Departments[Initials])

    and:

    =IFNA(IF(E3="","",INDEX(Staff[Expected Load],MATCH(E3,Staff[Initials],0))),"")

    Whilst getting this sorted, I would also like to have the macro copy values instead of formulae from A1, B2:U4 (instead of 2:4) and E94:U104.

    As always, your help is very much appreciated.

    Here's the VBA code:

    Please Login or Register  to view this content.
    I am using Office 365 on a Windows 10 machine (all up-to-date).

    Thank you!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,427

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Hi Ali,

    Long time no chat.

    Has the relevant worksheet changed position in the workbook? The code to convert Rows 2 to 4 was written to work with the first tab (i.e. furthest left) of the current workbook so if that's changed it will cause issues.

    That said, to convert your desired range to values you can use this:

    Please Login or Register  to view this content.
    Hope that helps,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Ah, OK - well, it never was the leftmost tab, so I wonder why it worked before? The tabs the macro is meant to work with are tabs 4 to 22 (if I've counted correctly). However, I realise looking at the VBS editor that it is in fact sheet 37, but it could be 38 or 39, etc.

    Where would I put the two lines you have given me to incorporate them into the current macro?

    Thanks for the help, Robert!

    EDIT: I should have pointed out that the tab selection is from a dialog box invoked prior to this macro running, and that bit works fine.
    Last edited by AliGW; 03-01-2019 at 06:18 AM.

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,427

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Ah, OK - well, it never was the leftmost tab, so I wonder why it worked before?
    Not sure Is the code creating anew workbook? If so and if that tab isn't the first it obviously won't convert Row 2 to 4 to values (it will on the first tab in the new workbook )

    Where would I put the two lines you have given me to incorporate them into the current macro?
    Replace (or comment out) this line...

    Please Login or Register  to view this content.
    ...with this:

    Please Login or Register  to view this content.
    Robert
    Last edited by AliGW; 03-01-2019 at 08:45 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    OK - progress. HOWEVER, it is now copying the value from A1 to all cells in the ranges stated, instead of the formula result from the ranges. It also seems to be un-merging A1:C1, which I don't want. I'm getting this:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    2
    Year Groups &
    Teaching Groups
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    3
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    4
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    5
    No. of periods
    per fortnight
    Totals
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    Sheet: Art

    Have I got something wrong in the code?

    Please Login or Register  to view this content.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    PS - No, it isn't un-merging those cells.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    So, if I change this:

    Please Login or Register  to view this content.
    to this:


    Please Login or Register  to view this content.
    I am back to square one. In other words, I get the result shown in post #1 above. Why is it not returning the values that the formulae return in the original sheet? What am I missing here?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    I'm guessing that the code is copying from the newly created workbook. If this is the case, then the formulae won't work because they are dependent on the original workbook. So, how do I get it to transfer those values from the original workbook to the new one at the point at which it creates the new file? Do I need to tweak the copy code or move it or both?

    Any insights would be welcome.

  9. #9
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,901

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Ali, if you copy a sheet within the workbook itself are the correct values displayed then ?

    Otherwise we could copy the worksheet to the end of the sheets, convert to values before we export it.

    At the end delete the copy within the workbook.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Hi, Rudi!

    Your question has made me think a bit more about this and I have realised that it may be a slight change I've made to the way the worksheet works that is probably the issue here (and it's a change I want to keep, so I need to find a way round it).

    Essentially, the initials in row 3 are populated using formulae that refer to cell H1 (they were typed in directly last year - this is the change I've made):

    =LOOKUP(2,1/((Departments[Department]=$H$1)*(Departments[HoD]="*")),Departments[Initials])

    Cell H1 is populated using this formula:

    =IFERROR(MID(@CELL("filename",A1),FIND("]",@CELL("filename",A1))+1,255),TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(SUBSTITUTE(@CELL("filename",A1),"/",REPT(" ",99)),"\",REPT(" ",99)),98),".xlsx","")))

    This makes sure that the cell gets its name from the new file's name once it's exported.

    Is there a way round this?

  11. #11
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,901

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Here goes nothing.
    Please Login or Register  to view this content.
    Last edited by bakerman2; 03-02-2019 at 02:40 AM.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Goeije morn, Rudi!

    Thank you for this - I shall give it a go right now.

    Ali

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Nope.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    1
    HoD Timetable Request - March 2019
    Return to AGW by Friday 29 March 2019
    Department: Art
    2
    Year Groups &
    Teaching Groups
    3
    4
    5
    No. of periods
    per fortnight
    Totals
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    6
    KS3
    40
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    Sheet: Art

  14. #14
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,901

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Missing a .value here.
    Please Login or Register  to view this content.
    As asked before, if you copy a sheet within the workbook itself are the values in the copy displayed correctly then ?

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    As asked before, if you copy a sheet within the workbook itself are the values in the copy displayed correctly then ?
    No, they can't do because the formula in E3 can't work because the tab name has changed, so H1 is not showing the correct value. That's what I was saying in post #10.

    What needs to happen is for JUST cell H1 to change from a formula to a value on the first copy, then for the named ranges to change to values on the second copy. Does this make sense?

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Quote Originally Posted by bakerman2 View Post
    Missing a .value here.
    Please Login or Register  to view this content.
    Bingo!!!!

    It is now working. Can you help with one last thing: I would like the focus in the source document to return to a specific workbook tab at the end of the process: how do I do that?

    Thanks for all of this!

  17. #17
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,901

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Last line in your code.
    Please Login or Register  to view this content.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Perfect - thank you so much once again!

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    Uh-oh!

    I spoke too soon ...

    It works for one sheet only. When I select multiple sheets, the first sheet splits out correctly, and then the rest are just copies of the last sheet in the workbook with cell H1 showing the correct (subject) value.

    I need it to loop through the sheets I select.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,372

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    This is in fact a different issue, so I'll start a new thread.

    Thanks again.

  21. #21
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,901

    Re: VBA to Split Workbook: No Longer Converting Formula Results to Values

    My bad.

    Put this part
    Please Login or Register  to view this content.
    just above next.

+ 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. [SOLVED] Split macro no longer working
    By PandoraGirl in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-07-2019, 05:33 PM
  2. [SOLVED] split formula of data not giving expected results need assistance
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-15-2018, 12:57 PM
  3. Replies: 1
    Last Post: 04-13-2017, 08:59 PM
  4. Replies: 9
    Last Post: 09-22-2015, 02:43 AM
  5. Formula to search all cells in a workbook for characters longer than 6
    By hoochoo in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-21-2015, 07:19 AM
  6. [SOLVED] Converting a formula into just the results
    By matt1020 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-14-2013, 03:06 PM
  7. [SOLVED] Formula needed to split the results of a DSUM from another worksheet
    By RandomNumber in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-31-2012, 10:50 AM

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