+ Reply to Thread
Results 1 to 7 of 7

Copy & Paste to worksheet if cell is not blank - loop

  1. #1
    Registered User
    Join Date
    03-16-2017
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    4

    Question Copy & Paste to worksheet if cell is not blank - loop

    Each row of my worksheet has 240 cells. I would like to copy and paste certain cells to another worksheet if they contain data. There can be up to four guests listed in each row and I would like to have each guest listed separately in a new row on a different sheet but only with select data from the original worksheet. This sheet is updated daily and the list will continue to grow.

    example: Cells A2:BH2 contain guest 1, BI2:DP2 contain guest 2, etc, etc. What I would like to happen is for Cells A2, B2, G2, K2, M2 to copy and paste into a new worksheet. Then look at BI2, if this cell is not blank then copy and paste some of the cells in that guests range into the next available row of the new worksheet. If the range of cells fro row 2 for guest 3 is blank then look at row 3 and do this same thing again.

    Thank you in advance for any help with this. I have tried and failed to get a macro to work.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Copy & Paste to worksheet if cell is not blank - loop

    It would help immensely to have a small sample workbook to look at and experiment with. Would it be possible to post one (use: Go Advanced --> Manage Attachments) with all of your headers and columns and maybe... 10 or so rows? Be sure to alter or remove any sensitive data.

  3. #3
    Registered User
    Join Date
    03-16-2017
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    4

    Re: Copy & Paste to worksheet if cell is not blank - loop

    I have attached the sample file.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Copy & Paste to worksheet if cell is not blank - loop

    This looks like it might even be possible without VBA, but I'm confused about what columns you want carried over to the mailing list. Your post mentions A2, B2, G2, K2, and M2, but A2 and B2 aren't listed for guests two through five, so what should be entered on Mailing list for them? Can you clarify the rule? e.g.: If "Last Name" is not blank, then copy Last Name (column I) and the address info (columns AA:AF) to the mailing list sheet; repeat for the other guests. Which specific headers/columns should be carried over to the Mailing List sheet for each guest?

  5. #5
    Registered User
    Join Date
    03-16-2017
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    4

    Re: Copy & Paste to worksheet if cell is not blank - loop

    I was just using those cells as a generic example. Basically just trying to create a contact list. Guest or attendee, First Name, Last Name, Assn Name, title, address, address 2, city, state, zip, country phone, email.

    This is in google sheets and updates through zapier automatically.

    We could export, sort, copy, paste, etc. Just trying to automate the process as registration is open for months.
    Last edited by taxchick1969; 03-17-2017 at 10:59 AM.

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Copy & Paste to worksheet if cell is not blank - loop

    Your headers for each guest must match if you want them copied to the mailing list, so I had to insert an attendee/guest column for guest 2 in column AH, and I changed the wording of the email header for guest 1 to match the other guests. Hopefully that's not problematic?

    Once that's done, I put the formula below in A2 of the Mailing List sheet. It must be array-entered (confirm it with Ctrl + Shift + Enter instead of the regular Enter). You can then fill it right through column M and down as far as you think you'll need it to go.

    =IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX('Master Data'!$A$2:$AG$147,SMALL(IF('Master Data'!$I$2:$I$147<>"",ROW('Master Data'!$I$2:$I$147)),ROW(1:1))-1,MATCH('Mailing list'!A$1,'Master Data'!$A$1:$AG$1,0)),INDEX('Master Data'!$AH$2:$BN$147,SMALL(IF('Master Data'!$AN$2:$AN$147<>"",ROW('Master Data'!$AN$2:$AN$147)),ROW(1:1)-COUNTIF('Master Data'!$I$2:$I$147,"<>"&""))-1,MATCH('Mailing list'!A$1,'Master Data'!$AH$1:$BN$1,0))),INDEX('Master Data'!$BP$2:$CR$147,SMALL(IF('Master Data'!$BW$2:$BW$147<>"",ROW('Master Data'!$BW$2:$BW$147)),ROW(1:1)-COUNTIF('Master Data'!$I$2:$I$147,"<>"&"")-COUNTIF('Master Data'!$AN$2:$AN$147,"<>"&""))-1,MATCH('Mailing list'!A$1,'Master Data'!$BP$1:$CR$1,0))),INDEX('Master Data'!$CS$2:$DT$147,SMALL(IF('Master Data'!$CY$2:$CY$147<>"",ROW('Master Data'!$CY$2:$CY$147)),ROW(1:1)-COUNTIF('Master Data'!$I$2:$I$147,"<>"&"")-COUNTIF('Master Data'!$AN$2:$AN$147,"<>"&"")-COUNTIF('Master Data'!$BW$2:$BW$147,"<>"&""))-1,MATCH('Mailing list'!A$1,'Master Data'!$CS$1:$DT$1,0))),INDEX('Master Data'!$DU$2:$EW$147,SMALL(IF('Master Data'!$EA$2:$EA$147<>"",ROW('Master Data'!$EA$2:$EA$147)),ROW(1:1)-COUNTIF('Master Data'!$I$2:$I$147,"<>"&"")-COUNTIF('Master Data'!$AN$2:$AN$147,"<>"&"")-COUNTIF('Master Data'!$BW$2:$BW$147,"<>"&"")-COUNTIF('Master Data'!$CY$2:$CY$147,"<>"&""))-1,MATCH('Mailing list'!A$1,'Master Data'!$DU$1:$EW$1,0))),"")

    I set the formula to account for rows 2 through 147 on Master Data. If you expect to have more/less than 147 rows of data, then do a replace all and replace $147 with something more suitable. I would advise making this change with JUST the formula in A2, then filling right and down again.

    The benefit to my approach is that no VBA/macros are used and the Mailing List sheet will automatically update as changes are made to Master Data, so you shouldn't need to touch the Mailing list page. The downside is that while you CAN filter the data on Mailing List, the data is all formula-generated, so sorting it is tricky, and might be best achieved with a pivot table or by copying and pasting the values only on another sheet.

    Take a look at the attached sample and see if it'll do the trick:
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-16-2017
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    4

    Re: Copy & Paste to worksheet if cell is not blank - loop

    I will test it out and let you know- thanks!

+ 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] Copy Cell From One Cell and Paste to next blank cell, Loop until last row/column
    By Beginner Level in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-01-2020, 02:38 AM
  2. Replies: 3
    Last Post: 01-09-2017, 05:22 PM
  3. Copy and paste from one worksheet to the first blank cell in another worksheet
    By Andrewstupendo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-27-2016, 03:25 PM
  4. Macro to update balance sheet. Trying to loop until row is blank & copy/paste.
    By sneaders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2014, 10:44 PM
  5. [SOLVED] Copy/Paste Loop Needs to Recognize Blank Cells
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2013, 11:50 AM
  6. Copy valule from one column and then paste only blank cell to other worksheet
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 12:42 PM
  7. Replies: 1
    Last Post: 04-15-2013, 08:06 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