+ Reply to Thread
Results 1 to 10 of 10

Copy, find blank cell, paste

  1. #1
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    37

    Copy, find blank cell, paste

    Hi guys,

    I was wondering if you could help me out with developing a macro code that enables to copy the range of cells (B207:AB207), find the first blank cell in Column B in another workbook, and then, paste values only into the range starting form the identified cell (B..:AB..). Ideally, I would like to copy just the non-blank cells from the initial range but I won't be too fussy if it's not possible at this stage.

    Thanks a lot for your help.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Copy, find blank cell, paste

    This is not hard to do. Would need workbook names and sheet names. Could you record a macro and manually copy\paste the data, then post the recorded macro code here? (Use CODE tags around your posted code. It's a Forum rule. See my signature block below.) The recorded code would answer a lot of the questions.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    37

    Re: Copy, find blank cell, paste

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Copy, find blank cell, paste

    Put this code in Sales workbook-T9.xlsm

    This copies to the first sheet in the Schedule workbook. It must be open in the same instance of Excel.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 04-12-2021 at 09:22 PM.

  5. #5
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    37

    Re: Copy, find blank cell, paste

    Thank you. Just to clarify, is there a chance to copy just the cells that are non-blank. For example, if the cell C213 is blank it won't be pasted into the 'Schedule' workbook?
    Also, could I add the command to make Excel open the Schedule file?

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Copy, find blank cell, paste

    What does it matter? The destination cell in Schedule is already blank.

  7. #7
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    37

    Re: Copy, find blank cell, paste

    I was thinking to have some formulas in columns for which cells will always be blank in the initial range. For example, I know that cell D207 will always be blank and would love to have the formula in column D in the Schedule file, not overwritten by pasting the empty D207.

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Copy, find blank cell, paste

    You can try to include the SkipBlanks argument, but it doesn't work in some situations.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    37

    Re: Copy, find blank cell, paste

    Thanks for your help. I'll give it a go and let you know the outcome.

  10. #10
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    37

    Re: Copy, find blank cell, paste

    Unfortunately, SkipBlanks doesn't work in this situation. Thank you very much for your help anyway. I will play around FormulaR1C1 and see what happens.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Search Range, find criteria, offset, copy and paste to blank cell in new specified range
    By fellodan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-14-2019, 01:42 PM
  2. VBA to copy various cells in sheet 2 and find next blank WHOLE row and paste
    By HACCStaff in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-07-2017, 11:25 PM
  3. [SOLVED] Find certain word, copy and paste cell in previous row if previous row is blank
    By steven_e in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 12:53 PM
  4. VBA Find first non-blank cell, then copy and paste the entire row
    By Misterturtle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2013, 12:49 AM
  5. [SOLVED] Find Blank lines, then copy and paste certain data
    By steven_e in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-21-2013, 10:20 AM
  6. [SOLVED] Find first non-blank cell after a group of blanks copy offset cells, find next blank, loop
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2012, 06:11 AM
  7. find last non blank cell in a column and copy paste in next row
    By prasad_tavva in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2007, 09:41 AM

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