UsedRange, copy paste values?

[YellowLabPro]

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:

[VBA]
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
[/VBA]

thanks,

doug

[Bob Phillips]

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]

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]

Excuse my ignorance here but shouldn't it be Destination:=Wst.Cells(1,1)?

[YellowLabPro]

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]

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]

Is the workbook you set to Wss visible?

[YellowLabPro]

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]

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:
[VBA] Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ur As String
Ur= Me.UsedRange.Address
Me.Range(Ur).Name = "MyRange"
End Sub[/VBA]then 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]

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]

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 [VBA]ActiveSheet.UsedRange [/VBA]you reference with the name it will be [VBA]Sheets("Sheet1").Range("A1:B52") [/VBA]where A1:B52 was perhaps the last time tha named range was assigned so it looks like [VBA]Sheets("Sheet1").Range("MyRange") [/VBA]its not the usedrange anymore its an actual range....is that any clearer?
So, using my suggestion you could use [VBA]Wss.Range("MyRange")[/VBA]

[YellowLabPro]

Simon,
If I follow, I would place your code in my Source Worksheet, which is "DataEdited", then run my code from a module?

Mycode:
[VBA]
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
[/VBA]

[mvidas]

UsedRange is a range.. you'd get Invalid Use of Property is you tried doing something like [vba]wss.UsedRange[/vba]without 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:[vba] Wss.UsedRange.Copy Wst.Range("A1")
'or
With Wss.UsedRange
Wst.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With[/vba]Matt

[YellowLabPro]

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]

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]

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:[vba]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 Sub[/vba]Matt

[YellowLabPro]

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]

Honestly I can't say for sure why a sub like this works:[vba]sub randomsubname()
activesheet.usedrange
end sub[/vba]
Or what it is doing.. seems to be an anomoly. As for using a worksheet variable, you could just do:[vba] Dim i As Long
i = Wss.UsedRange.Rows.Count[/vba]and it will 'reset' the last cell the same way.

[mvidas]

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]

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?
[VBA]

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

[/VBA]