Excel 2016 VBA compatibility (sheet selection/pasting)

[Fayebaline]

Hello

New here and wondered if I could have some help please?

I have just upgraded to 2016 and have come across an issue with some vba that has previously been working fine. I am at a dead end as to why it is no longer working, if someone could shed some light I shall be eternally grateful

So, the VBA is copying data from one worksheet and then pasting it into another, without selecting the second sheet, then it copies some other data and does the same.
The issue is that it has started activating the 2nd sheet and then the rest of the macro runs on the wrong sheet! The bizarre thing about it is that the first copy/paste works but the second doesn't, even when the code is exactly the same for both?!

Example code:
Range("OnsitePlanErrors1").Copy
Sheets("Errors! - Hours Import").Range("l65536").End(xlUp).Offset(1, 0).PasteSpecial xlValues
Range("OnsitePlanErrors2").Copy
Sheets("Errors! - Hours Import").Range("M65536").End(xlUp).Offset(1, 0).PasteSpecial xlValues

The sheet selection only changes on the second instance.. Can anyone help me please?

Thank you
Faye

 

[dmt32]

Hi,
welcome to forum.

You seldom have to select or activate a worksheet when using VBA - However, it would help to identify the problem you have if you could post the whole code you are using.


Dave

 

[John_w]

I'm unable to reproduce the behaviour you're seeing.

Check in Formulas tab -> Name Manager that the named ranges "OnsitePlanErrors1" and "OnsitePlanErrors2" are referring to the correct cells. Is the scope (Workbook or Sheet) correct?

The issue is that it has started activating the 2nd sheet and then the rest of the macro runs on the wrong sheet!

The solution is for the rest of the macro to not rely on the default (active) sheet, but to explicitly qualify all range references with the correct worksheet. However, try changing your code as follows to save the currently active sheet before the copy/paste and then reactivate it so that you don't have to change the rest of the macro.

    Dim currentSheet As Worksheet
    Set currentSheet = ActiveSheet
    
    Range("OnsitePlanErrors1").Copy
    Sheets("Errors! - Hours Import").Range("l65536").End(xlUp).Offset(1, 0).PasteSpecial xlValues
    Range("OnsitePlanErrors2").Copy
    Sheets("Errors! - Hours Import").Range("M65536").End(xlUp).Offset(1, 0).PasteSpecial xlValues
    
    currentSheet.Activate
    
    'Rest of the macro should now operate on the correct sheet

PS please use CODE tags when posting VBA code - the # icon in the message editor.

 

[Fayebaline]

hi both

Thank you for your replies and sorry for not using the code tags... I didn't realise..

Do you know why this would have stopped working, as Dave said, I didn't think you had to activate or select a sheet to paste to it.. I didn't want to go and amend all of my previously working macros to reactivate the first sheet when they shouldn't swap to the other one anyway..

It is only in excel 2016 that this is happening, are you reproducing it in that version? It's started happening in all my macros that immediately paste again over to another sheet without activating..

Thanks
faye

 

[Fayebaline]

Sorry, also, I have just checked and it is happening in vba where I have not used named ranges and just range themselves..

It's so annoying! Just don't know why excel 2016 has changed this!

 

[John_w]

It is only in excel 2016 that this is happening, are you reproducing it in that version? It's started happening in all my macros that immediately paste again over to another sheet without activating..

Yes Excel 2016, but I couldn't reproduce the problem.