Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 6 of 6

Thread: Macro copy and paste in next blank cell

  1. #1
    Join Date
    22nd July 2004

    Macro copy and paste in next blank cell

    I currently have set up a data collection template in excel for Project managers to complete. Once this is returned to me all this data needs to be copied into another spreadsheet (One row per project) ready for importing into excel.

    I have a simple macro that will copy into the first row only but I would like to be able to copy many of the questionaires into the second worksheet before importing to access, to do that the macro will need to copy into the next blank cell.............ideas

    The whole copy paste macro is made up of about 30 of these commands for different cells pasting into the same row of the sheet "SOA_ACCESS_TEMPLATE"

    Application.CutCopyMode = False
    Windows("SOA_ACCESS TEMPLATE.xls").Activate
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    I need to add something into the code that will paste in the next Blank cell ?

    Any ideas would be appreciated as I have not had much experience with VBA.

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    28th January 2003
    Solihull - England
    Post you rcurrent code - it will help us to amend it for your circumstances
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  3. #3
    Join Date
    17th September 2004

    Re: Macro copy and paste in next blank cell

    There was no response to this one, but this is exactly what I need to do as well. I need to combine two different tabs into one. I have a tab copying over to a sheet columns A-K or something like that, and then I have another tab that copies over, but I want to find the next blank row in column A to paste to. Is there a way to just select the next row down after the first initial paste with Active Cell Offset? and then select the other tab, copy what I need to, select the sheet to paste to and paste? Thank you very much for any insight you could provide!!

    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    20th May 2003
    Mesa, Az

    Re: Macro copy and paste in next blank cell

    The code below will find the last used row. Use it in your vba code and you can set your target for pasting to:

    Cells(xllastrow + 1, 1) 
    to find the first empty row with the focus set to column A.

    Function xlLastRow(Optional WorksheetName As String) As Long 
         '    find the last populated row in a worksheet
        If WorksheetName = vbNullString Then 
            WorksheetName = ActiveSheet.Name 
        End If 
        With Worksheets(WorksheetName) 
            xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _ 
            xlWhole, xlByRows, xlPrevious).Row 
        End With 
    End Function 
    Check out this link to see an example.


  5. #5
    Join Date
    19th November 2010

    Re: Macro copy and paste in next blank cell


    Everytime I try to use that code I keep getting an error At

    Range(cells(xlLastRow + 1, 1)).Select 
    I how to fix this. Please help
    Last edited by AAE; November 19th, 2010 at 05:53. Reason: add code tags

    Excel Video Tutorials / Excel Dashboards Reports

  6. #6
    Join Date
    19th January 2007
    South Carolina, USA

    Re: Macro copy and paste in next blank cell

    Hello griderae,

    Welcome to Ozgrid.

    Posting your question in threads started by others is a violation of the Forum Rules and is known as thread hijacking. Posting solutions is acceptable.

    ALWAYS start a new thread for your questions. You may, if you find it helpful, provide a link back to this (or any other) thread.

    Start a new thread and be sure to give it a title using only search friendly key words that accurately describe your thread content or overall objective and avoid the use of non-essential words such as "I need", "Help", "urgent", "desperate", etc.


    Per the Forum Rules, VBA code must be wrapped in code tags, which you omitted.
    I've added the tags for you this time; please be sure to use them in future posts when needed.

    Omission of code tags may result in future threads being locked without notice. See the link in my signature for more information on how to use tags.

    How to use code tags
    Starting tag > > [code]
    Your code here
    Ending tag > > [/code]

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Copy & Paste Between Two Sheets Until Blank Cell In Column
    By vpschroeder in forum Excel General
    Replies: 3
    Last Post: January 13th, 2012, 20:45
  2. VBA To Copy Range & Paste To First Blank Column
    By tommyboy444 in forum Excel General
    Replies: 6
    Last Post: April 7th, 2009, 01:25
  3. Search For Non Blank Cells, Copy And Paste
    By MackeyDExcel in forum Excel General
    Replies: 6
    Last Post: May 10th, 2007, 20:20
  4. Copy & Paste values without blank cells.
    By countryfan_nt in forum Excel General
    Replies: 5
    Last Post: July 30th, 2006, 00:49
  5. Replies: 5
    Last Post: March 24th, 2006, 15:01


Posting Permissions

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