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

[AliGW]

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!

[Trebor76]

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

[AliGW]

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.

[Trebor76]

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

[AliGW]

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.

[AliGW]

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

[AliGW]

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?

[AliGW]

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.

[bakerman2]

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.

[AliGW]

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?

[bakerman2]

Here goes nothing.

Please Login or Register  to view this content.

[AliGW]

Goeije morn, Rudi!

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

Ali

[AliGW]

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

[bakerman2]

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 ?

[AliGW]

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?

[AliGW]

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!

[bakerman2]

Last line in your code.

Please Login or Register  to view this content.

[AliGW]

Perfect - thank you so much once again!

[AliGW]

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.

[AliGW]

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

Thanks again.

[bakerman2]

My bad.

Put this part

Please Login or Register  to view this content.

just above next.