PDA

View Full Version : UsedRange, copy paste values?



YellowLabPro
05-23-2007, 10:23 PM
I have a dataset on a sheet in a different workbook, there are three columns separating the next set of data.

To make a very easy copy/paste, I wanted to use the Usedrange approach.

How does the usedrange know which range of data to use? I have not seen anything in the Help file that indicates this.

Also since I am using variables to qualify the worksheets my syntax is slightly different than the help file examples, and it fails:


Sub Index_Records()
Dim Wsb As Workbook
Dim Wss As Worksheet, Wst As Worksheet
Set Wss = Workbooks("MasterImportSheetWebStore.xls").Worksheets("DataEdited")
Set Wst = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")
Wss.UsedRange.Copy Destinaton:=Wst("A1").Paste
End Sub


thanks,

doug

Bob Phillips
05-24-2007, 12:16 AM
Excel keep track of the first used cell and the last used cell (badly in the latter case), so it is easy to figure out the UsedRange from this.

When using Copy Destination:=, you don't need to add .Paste.

YellowLabPro
05-24-2007, 03:43 AM
Bob,
no matter which syntax I use- fully qualified w/ the workbook and worksheet names or the variable names, I get the error message: Compile error: Named argument not found and it highlights Destination:=
Wss.UsedRange.Copy Destinaton:=Wst.Range(1, 1)

Simon Lloyd
05-24-2007, 03:47 AM
Excuse my ignorance here but shouldn't it be Destination:=Wst.Cells(1,1)?

YellowLabPro
05-24-2007, 04:04 AM
Thanks Simon,
That does work, but I had a very silly error, I had Destination spelled wrong.... I had it w/out an "i". That is why I was getting the named argument error.

Thanks Simon, Thanks Bob

YellowLabPro
05-24-2007, 04:08 AM
One more question w/ regards to UsedRange.
I was reading from a post on MrExcel.com from Ivan Moala that UsedRange is quirky and to help keep things straight he recommends to do this-
Activesheet.UsedRange
I used this while my source sheet was active, but not always the case where the ActiveSheet will be the source sheet, I did this:
Wss.UsedRange
But this returned an error: Invalid use of property
Why does the first one work and the second one fail?

thanks,

Doug

Simon Lloyd
05-24-2007, 04:16 AM
Is the workbook you set to Wss visible?

YellowLabPro
05-24-2007, 04:22 AM
Simon,
I should have written more clearly, the source sheet will not always be the visible or activesheet. There can/will be times when the target sheet, Wst, will be active/visible.
That is the reason for using something other than ActiveSheet.UsedRange

Simon Lloyd
05-24-2007, 04:33 AM
if you are specifying the worksheet i don't think it has to be the activesheet to use the UsedRange property,you cannot reference (AFAIK) a usedrange in a closed workbook. In your other workbooks you can set a named range to the used range automatically like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ur As String
Ur= Me.UsedRange.Address
Me.Range(Ur).Name = "MyRange"
End Subthen perhaps you could reference a range like Sheets("Sheet1").Range("MyRange") but you will probably have to use ADO to read from a closed workbook!

YellowLabPro
05-24-2007, 04:58 AM
Thanks Simon,
I hope I have not instructed you incorrectly. Both workbooks are open, so accessing the closed book is not an issue, yet....:)

I will work w/ your idea here.
But in thinking about it, what the error message was- does not support this property, UsedRange is an all memory thing, as Bob pointed out. Since this is the case, using the statement Wss.UsedRange instead of ActiveSheet.UsedRange, fails now makes sense.
The ActiveSheet and UsedRange are what Windows is seeing and the property does not support a qualified address. At least this seems like a reasonable explanation to me.

Simon Lloyd
05-24-2007, 05:05 AM
YLP if you use my suggestion with the named range thing it should never be a problem as the Usedrange is given a name everytime the worksheet is changed, therefore it will never be ActiveSheet.UsedRange you reference with the name it will be Sheets("Sheet1").Range("A1:B52") where A1:B52 was perhaps the last time tha named range was assigned so it looks like Sheets("Sheet1").Range("MyRange") its not the usedrange anymore its an actual range....is that any clearer?
So, using my suggestion you could use Wss.Range("MyRange")

YellowLabPro
05-24-2007, 05:54 AM
Simon,
If I follow, I would place your code in my Source Worksheet, which is "DataEdited", then run my code from a module?

Mycode:

Sub Index_Records()
Dim Wbt As Workbook
Dim Wss As Worksheet, Wst As Worksheet
Dim LRow As Long
Set Wbt = Workbooks("TGSItemRecordCreatorMaster.xls")
Set Wss = Workbooks("MasterImportSheetWebStore.xls").Worksheets("DataEdited")
Set Wst = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Dupe Finder")
LRow = Wst.Cells(Rows.Count, 1).End(xlUp).Row
Wst.Range("A1:I" & LRow).ClearContents
'Wss.UsedRange
'Wss.UsedRange.Copy Destination:=Wst.Cells(1, 1)
'Wst.Range("A1:I").Value = Wss.UsedRange
'Wbt.Wst ("A1")
Wss.Range("MyRange").Copy Destination:=Wst.Cells(1, 1)
End Sub

mvidas
05-24-2007, 08:12 AM
UsedRange is a range.. you'd get Invalid Use of Property is you tried doing something like wss.UsedRangewithout anything else for it.. would be the same as just putting "Cells" on a line by itself.

Do you have a link to the mrexcel thread where Ivan talks about usedrange and problems with it? I use it frequently and have never come across an issue with it

FWIW, either of the following should work for you: Wss.UsedRange.Copy Wst.Range("A1")
'or
With Wss.UsedRange
Wst.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End WithMatt

YellowLabPro
05-24-2007, 08:39 AM
Matt,
Here is the link, it is the last of the posts on the page.
http://www.mrexcel.com/archive/VBA/15835.html

I got locked out of the board today by trying to fix my email program. I just got back up and running.
I was working on what you were helping me w/ y-day and will post there in a few.

thanks,

doug

YellowLabPro
05-24-2007, 08:47 AM
Matt,
Simon looks to be signed off. So that I can put one thing to bed, can you tell from Simon's code, where it is supposed to be stored and run?

mvidas
05-24-2007, 08:48 AM
I think Ivan might have been confused about what he was referring to, I'm guessing he was first talking about the SpecialCells(xlLastCell) which is definitely quirky, and can be reset by any call to the used range

Consider the following:Sub LastCellUsedRangeexample()
'create 1 sheet workbook
Workbooks.Add -4167
MsgBox Cells.SpecialCells(xlLastCell).Address 'msgbox last cell's address
'blank, so address is A1

'add something to a cell
Range("J10").Value = "blah"
MsgBox Cells.SpecialCells(xlLastCell).Address 'msgbox last cell's address
'J10 now contains data, so it will be address

Range("AH2483").Value = "blah blah"
MsgBox Cells.SpecialCells(xlLastCell).Address 'msgbox last cell's address
'AH2483 now contains data, so it will be address

Range("AH2483").ClearContents
MsgBox Cells.SpecialCells(xlLastCell).Address 'msgbox last cell's address
'AH2483 no longer contains anything, but is still last cell since
' SpecialCells just looks at what was used

Dim x As Long
'calling the usedrange (getting a .rows or .columns or .cells count
' doesnt actually do anything other than force excel to look at
' the sheet again
x = ActiveSheet.UsedRange.Rows.Count
MsgBox Cells.SpecialCells(xlLastCell).Address 'msgbox last cell's address
'J10 is now the correct last cell since the sheet was 'refreshed'
End SubMatt

YellowLabPro
05-24-2007, 08:52 AM
This will take a little time to play with.
Let me backtrack- Based on Ivan's post, what I thought was happening by having the line;
Wss.UsedRange
was sort of like setting the UsedRange to this sheet, so later on in my code it knew where to look.
If I use ActiveSheet.UsedRange, it works as expected.

mvidas
05-24-2007, 09:23 AM
Honestly I can't say for sure why a sub like this works:sub randomsubname()
activesheet.usedrange
end sub
Or what it is doing.. seems to be an anomoly. As for using a worksheet variable, you could just do: Dim i As Long
i = Wss.UsedRange.Rows.Countand it will 'reset' the last cell the same way.

mvidas
05-24-2007, 09:24 AM
As for Simon's code, there is no "Me" references in there and it is not a worksheet/workbook event, so a standard module would be fine.

YellowLabPro
05-24-2007, 09:32 AM
Sorry to keep hitting you from all different sides, (this is Simon's code), Where do I put this- which sheet, source or target and where is it run from?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ur As String
Ur= Me.UsedRange.Address
Me.Range(Ur).Name = "MyRange"
End Sub

Simon Lloyd
05-24-2007, 09:35 AM
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
05-24-2007, 09:37 AM
It seems I looked at the wrong code when I made my last comment :shifty: oops :)
Personally I don't think thats necessary, but whatever works. Far be it for me to call anything overkill :)

Simon Lloyd
05-24-2007, 09:49 AM
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
05-24-2007, 09:53 AM
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: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

Simon Lloyd
05-24-2007, 10:39 AM
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.