VBA to set range name for later use in macro.

[JonnyBT123]

Morning all,
Racking my brains with this one as I’ve used this techniquemany of times in many different workbooks and macros.
Below is a snip of the code I’m using, which this part is tocopy the whole worksheet (named “WorkCompleted”) to values, and then highlightcolumns E:I to the last row of data there (which will be variable, as therecould be 6 rows, there could be 50 rows etc.) and name it for later use. Row 1is the header of the table, and everything else below is data.

 With Worksheets("WorkCompleted")

.Cells.Copy
.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

.Range("E1",Range("I100").End(xlUp)).Select
    Set rngWC =Selection

End With

It bugs out on the .Range(“E1”,Range(“I100”).End(xlUp)).Selectand goes to my cleanup error part, and I just don’t understand why. I’veliterally used this so many times in other macros.
Apologies if this isn’t the best, I’ve made this code fromscratch and left out the parts that work and I deem irrelevant to put in the post,but if required, I can paste the full macro.
Any help would be appreciated! I must be missing somethingso simple L

(PS: Not sure why the code is split out into two sections asabove…. Sorry!)

 

[steve the fish]

If you are using a With statement your range needs to be .Range ie Range("I100") needs to be .Range("I100") however you cannot select a range that isnt on the activesheet lets not use the Select at all.

Set rngWC = .Range("E1", .Range("I100").End(xlUp))

 

[JonnyBT123]

Knew it was something simple I'd missed! You sir, are a lifesaver. Thank you for your prompt response. Works a charm