Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: VBA help to Select and Copy

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this

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

    Hope this helps!

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.




  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •