+ Reply to Thread
Results 1 to 11 of 11

Find next empty cell in range and paste

  1. #1
    Registered User
    Join Date
    11-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Find next empty cell in range and paste

    hi, very new to this. I am looking to copy a coumn of data from one worksheet to another and paste it in a range of cells in the next available column.

    Any help appreciated

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Find next empty cell in range and paste

    Hi Friday,

    Copying data is relatively simple to do via macro, but you have to tell us which column of data you want copied. It doesn't need to be the same column every time, but the macro has to understand why you want to move a column. Will you be selecting the column first, then running the macro, or should the macro have some other knowledge of your worksheets?

  3. #3
    Registered User
    Join Date
    11-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Find next empty cell in range and paste

    Thanks

    Ok I would like to copy from worksheet 'Input' D8 to D16 and then paste special that data in the next available column in worksheet 'analysis' in the range of A4:M17.

    I hope this is clear?!

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Find next empty cell in range and paste

    Hi,

    Could you post an example workbook with a before and after sheet? I just can't grasp the concept by the description.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  5. #5
    Registered User
    Join Date
    11-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Find next empty cell in range and paste

    THis is sortof what I am chasing (my folder is far more indepth than this) but its an example of what I am looking for.

    Thanks for your response
    Attached Files Attached Files

  6. #6
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Find next empty cell in range and paste

    I had something written, but now see your sample data. Not quite what you specified earlier, but this should work. It assumes that you already have headers in the first row of the Analysis worksheet (month names) so it's looking for the last value in the second row of that sheet, and putting the new data in the next column to the right starting in row 2.
    Please Login or Register  to view this content.
    Hope that helps!

  7. #7
    Registered User
    Join Date
    11-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Find next empty cell in range and paste

    Thanks for your help, its all really over my head so might have to figure this out some other way? I really need a step by step explination to really convert the data to apply to my actual spreadsheet that I am using- its for work purposes so I can not just post it up.

    Thank you I appreciate your time

  8. #8
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Find next empty cell in range and paste

    If your actual spreadsheet is laid out exactly as your sample spreadsheet (perhaps with more rows and columns, but same layout), then the macro should work for you.

    See an updated copy of your sample worksheet. I added a module in the VB Editor (ALT+F11) with the code I provided earlier. I left just the Input and Analysis sheets.

    To run the macro, press ALT+F8, select 'copyData' and click Run. The data will then appear on the Analysis worksheet and disappear from the Input worksheet. Add new data to the Input sheet and run the macro again, and the data will appear in the next column over on Analysis.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Find next empty cell in range and paste

    Thank you.......

  10. #10
    Registered User
    Join Date
    11-15-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Find next empty cell in range and paste

    Sorry to be painful. ok I have made up a spreadsheet that is a better representation of what I have done.

    You will see the table in the Analysis sheet and I want the data (from the input sheet under the Data Totals column) to paste in the next empty range within that table (above the graph) eg: May's data to go in there etc etc.

    Thanks
    Attached Files Attached Files

  11. #11
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Find next empty cell in range and paste

    The first macro below acts like my previous code, in that it doesn't actually copy/paste data, it sets one range of cell values equal to the input data, then clears the input data. The second macro actually copies the input data from A2:A13 (12 cells) and pastes it into the next available column in your table (formatting and all). You can get around this by using PasteSpecial in a macro instead, but that's up to you.

    Another issue is that your table data started in row 5, not 2. Hopefully you've seen how providing accurate sample data and layouts help move things along faster, especially when you don't know how to adjust the macro to your needs.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

+ 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