Try using worksheet references for the ranges, without it VBA will assume the ranges you want to use are on what it considers the active sheet.
If you are lucky that will also be the worksheet you want the code, if you aren't it won't be and the code might not work properly.
You've got a With statement with a worksheet reference but you never actually use it.
You also have a reference to a worksheet earlier in the code that you use for the first part.
Why not try using that throughout the code?
The first thing to do might be to replace this.
With this.Code:With Xl.Application.ActiveWorkbook.ActiveSheet
Then in the rest of the code add a few dot qualifiers and drop the Select/Selection stuff.Code:With Xlsheet
Something like this perhaps.
Code:With Xlsheet Set rng = .Range("E6") ' Code to test whether the data is one cell only, or more than one cell If IsEmpty(rng.Offset(-1, 0)) Then rng.Copy .Range("F5").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Else Set rng = Range(rng, rng.End(xlDown)) 'Selects the entire range rng.Copy .Range("F5").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End If End With