VBA help to Select and Copy

[chookers]

Hello there.

I've got some code here (done with some of your help) to find the last row on a worksheet. What I have been trying to do is to now select and copy rows 1 through to the "last row". And I can't bloody make it work! Normally you'd select Rows 1:25 or whatever, but I just can't work out where and how exactly to put it all together. I'm eventually going to paste it to another sheet and reformat it, which I can handle, but of course this one stupid bit which should be easy has me stumped. So can anyone tell me how (and where to put!) code which will select (and copy) rows 1:endof list. I am feeling quite inept at the moment. As specific as you can get will not go astray.

Here's what we have so far:

Sub FindLastRow()
Dim lastrow As String
Dim EndofList As String

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching back from Rows.
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
EndofList = lastrow
Range("A1").Select
ActiveCell.Offset(EndofList, 1).Range("A1").Select

End Sub

Thanking you!!!

[Dave Hawley]

Hello Chookers

Try this

Sub FindLastRow()
Dim rlast As Range


If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching back from Rows.
Set rlast = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If

Range("A1", rlast).Copy Destination:=Sheet5.Range("A1")
End

End Sub

[chookers]

Thanks Dave. Yes that makes sense but when I try it it wants me to debug the last .row just before the End If. It says compile error - type mismatch. I'm actually on my way out the door to work tomorrow so will try some more tomorrow.

Cheers!

[Dave Hawley]

Sorry Chookers, forgot to remove the Row Property from the Find Method


Sub FindLastRow()
Dim rlast As Range


If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching back from Rows.
Set rlast = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
End If

Range("A1", rlast).Copy Destination:=Sheet3.Range("A1")
End

End Sub

Now Sheet5 is a CodeName of a Sheet. You may well have to alter this to suit.

[chookers]

Hi Dave, I tried the new code but now it doesn't like the 'set rlast=' row. It says "Unable to get the find property of the range class".

am assuming for your "sheet5" I just substitute my sheet name instead? I actually tried that but it didn't like that either.

Give me a gun.

Any other thoughts? Thanks in advance, Lauren aka Chookers

[Cosmos75]

Try this

Range("A1", Range("A65536").End(xlUp).Address).Copy

Hope this helps!

[chookers]

Thanks very much, I will - have to go tutor some mad kids for a couple hours first though! Upon looking at that code some more, maybe the problem is something silly in the last line - The 'Sheet3' No matter what I put there it says variable not defined, what do I do for this? Do I have to do a Dim As String. God almighty, give me Access any day. I like this, but the frustration factor of learning under pressure is wicked!

Will check back and see if I can sort it out. By the way the sheet the code is meant to copy from is called "My Class", and the sheet I want it to past to is called "Activities".

Thank you!

[chookers]

Cosmos tried your code at the bottom and it gives me the 'unable to get the find property of the range class' error for the Set rlast line of code.

Survived the kids, can't survive Excel code!

Help!

[Dave Hawley]

Hi chookers

Sheet5 is a sheets CodeName, these can been seen in the "Project Explorer" they are the names NOT is brackets.This is the best means of referencing Worksheets as the CodeName cannot be changed.

You say you tried the new code but it doesn't like the:

"'set rlast=' row" There is no "row" in the NEW code???

Sub FindLastRow()
Dim rlast As Range


If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching back from Rows.
Set rlast = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
End If

Range("A1", rlast).Copy Destination:=Sheet3.Range("A1")
End

End Sub


For "Sheet3" use either the CodeName of you sheet or replace it with it's Tab name, eg Sheets("Sheet1")

Are you running this via a CommandButton from the Control toolbox? If so set it's "TakeFocus*******" Propert to False.

You could use just:
Sub CopyAllData()
ActiveSheet.UsedRange.Copy Destination:=Sheet5.Range("A1")
End Sub

But this method is very unreliable,see:
http://www.ozgrid.com/VBA/ExcelRanges.htm


If it all get's too hard just email the Workbook.

[chookers]

Hi Dave, thanks for that. Will try it in about an hour when I'm free again. I did take the "row" out of the code, I just meant row as in row, you know, not row as in row being code. Just row 3 of the code. Am going to run this just from a button on a toolbar.

Gotta run, will let you know what happens!

Cheers!