UsedRange, copy paste values?

[Simon Lloyd]

It goes in your source worksheet, its purely to give you an up to date usedrange reference by giving the new usedrange the same name each time

[mvidas]

It seems I looked at the wrong code when I made my last comment oops
Personally I don't think thats necessary, but whatever works. Far be it for me to call anything overkill

[Simon Lloyd]

Lol! Matt i was just trying to facillitate him having a named range to call on (easier than UsedRange) where that named range is always updated everytime the source sheet s updated....i thought that it may be easer to use in his coding!

[mvidas]

Makes sense.. though using Wss.UsedRange in place of Wss.Range("MyRange") would prevent the code from running all the time I can see a use for it if you wanted to have a named range based on the used cells of a specific column, could be quite useful with formulas referencing a column (though dynamic formulae would work just as well, and without VBA)

Taking your idea one step further, you could do the same for all sheets in a workbook by placing the following in the ThisWorkbook module:[vba]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Ur As String
Ur = Sh.UsedRange.Address
Sh.Range(Ur).Name = "MyRange"
End Sub[/vba]

[Simon Lloyd]

of course!, you then only have to know the sheet name you're working with as the named range will be the same for all sheets.