+ Reply to Thread
Results 1 to 3 of 3

Find next empty row and copy and paste information

Hybrid View

Gavin Ling Find next empty row and copy... 05-19-2009, 06:26 PM
DonkeyOte Re: Find next empty row and... 05-20-2009, 02:28 AM
Gavin Ling Re: Find next empty row and... 05-20-2009, 03:50 AM
  1. #1
    Registered User
    Join Date
    01-04-2006
    Posts
    70

    Find next empty row and copy and paste information

    Hi.

    I want to insert some data in a number of cells (e.g. A1, B4, C4 and H8) on a worksheet ('Sheet 1').

    I then want to press a button and have some code that:

    Looks at column B on 'Sheet 2'
    Finds the next empty row
    Copies and pastes the information from Sheet 1 into specific cells in that empty row.

    Any ideas?

    Gavin
    Last edited by Gavin Ling; 05-20-2009 at 03:51 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Find next empty row and copy and paste information

    Gavin, in truth I think we would want more info in terms of where you want to paste the resulting values...

    There are countless approaches to this type of thing... one (inelegant) approach being:

    Public Sub CopyData()
    Dim ws As Worksheet, bi As Byte, vData(1 To 4)
    Set ws = Sheets("Sheet1")
    For bi = 1 To 4
        vData(bi) = Application.Choose(bi, ws.Range("A1"), ws.Range("B4"), ws.Range("C4"), ws.Range("H8"))
    Next bi
    Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(, 4).Value = vData
    Set ws = Nothing
    End Sub
    Last edited by DonkeyOte; 05-20-2009 at 02:40 AM. Reason: incorrect sheet reference

  3. #3
    Registered User
    Join Date
    01-04-2006
    Posts
    70

    Re: Find next empty row and copy and paste information

    That's great. It seems to work fine.

    What I'm doing is typing an invoice in one page and I then want to store the information in list form on the other sheet once you hit the button.

    I've expanded it to copy all the cells I need (about 25 of them) and it seems to work perfectly.

    Job done - thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1