+ Reply to Thread
Results 1 to 14 of 14

Copy data from one sheet to another with a blank cell available.

  1. 04-19-2023, 12:45 PM #1
    Ariful Islam
    Ariful Islam is offline
    Forum Contributor
    Join Date
    02-16-2017
    Location
    Bangladesh
    MS-Off Ver
    2021
    Posts
    196

    Copy data from one sheet to another with a blank cell available.

    The quantity number mentioned in the 'Distribution!F7:F156' range of the 'Distribution' sheet should be taken from the 'CalculationDA14:DA*' range of the 'Calculation' sheet and placed in the 'CalculationDH14:DH*' range of the 'Distribution' sheet, including blank cells equal to the number specified in the Distribution!W14:W156' range of the calculation sheet, as shown in the attached file.

    The detailed steps will be as follows..
    Step-1:
    First check the range 'Distribution!F7'; if 'non-blank' cells are found, then go to step 2. If it is blank, then check if the next cell (Distribution!F8') is 'non-blank.
    In this case, it is 10
    Step-2:
    The number of data that should be copied from the range "Calculation! DA14:DA*" and placed in the range "Calculation!DH14:DH*" in sequence as the same as the number of data that are mentioned in the cell "Distribution!F7."
    e.g
    671457
    672284
    671458
    672285
    671459
    672286
    671461
    672287
    671462
    672289

    Step-3:
    check the range 'Distribution!W7'; if 'non-blank' cells are found, then that number of blank cell should be blank in the range "Calculation!DH14:DH*" in sequence as the same as the number of data that are mentioned in the cell "'Distribution!W7'" after following Step-2.

    Step-4
    Thus, for the next cell, step-1 to step-3 must be followed.

    This requires a VBA code. Color has been used for easy presentation of the subject, it is not necessary. I will be grateful if anyone can help me.
    Attached Files Attached Files
    Last edited by Ariful Islam; 04-21-2023 at 10:13 AM.
    Reply With QuoteRegister To Reply
  2. 04-21-2023, 11:37 AM #2
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data from one sheet to another with a blank cell available.

    Here is a possible solution for you. It's based on your original post. It uses the Column X in Distribution and column CZ in Calculation as inputs.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 04-21-2023, 01:54 PM #3
    Ariful Islam
    Ariful Islam is offline
    Forum Contributor
    Join Date
    02-16-2017
    Location
    Bangladesh
    MS-Off Ver
    2021
    Posts
    196

    Re: Copy data from one sheet to another with a blank cell available.

    The solution is working fine. I feel bad to myself why I went to enter this unnecessary serial number in the 'CZ' column of the 'Calculation' sheet, otherwise it would have been solved beautifully in one go. I manually entered the serial number in the 'CZ' column of the 'Calculation' sheet, so that I can highlight my problem easily. Originally given this serial number from column 'F' of 'Distribution' sheet. Would be grateful if you could please solve without considering serial number in 'CZ' column of 'Calculation' sheet.
    Reply With QuoteRegister To Reply
  4. 04-21-2023, 02:00 PM #4
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data from one sheet to another with a blank cell available.

    I will need to determine how many seats are allocated, should I take that information from Column F?
    Reply With QuoteRegister To Reply
  5. 04-21-2023, 02:10 PM #5
    Ariful Islam
    Ariful Islam is offline
    Forum Contributor
    Join Date
    02-16-2017
    Location
    Bangladesh
    MS-Off Ver
    2021
    Posts
    196

    Re: Copy data from one sheet to another with a blank cell available.

    Yes, the amount of seats 'Allocated' can be confirmed from column 'F' of the 'Distribution' sheet.
    Reply With QuoteRegister To Reply
  6. 04-21-2023, 02:25 PM #6
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,301

    Re: Copy data from one sheet to another with a blank cell available. 

    Please Login or Register  to view this content.
    from file you posted earlier.
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  7. 04-21-2023, 02:28 PM #7
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data from one sheet to another with a blank cell available.

    Here is the update to my code. 

    Please Login or Register  to view this content.
    Last edited by maniacb; 04-21-2023 at 04:42 PM.
    Reply With QuoteRegister To Reply
  8. 04-21-2023, 02:49 PM #8
    Ariful Islam
    Ariful Islam is offline
    Forum Contributor
    Join Date
    02-16-2017
    Location
    Bangladesh
    MS-Off Ver
    2021
    Posts
    196

    Re: Copy data from one sheet to another with a blank cell available.

    It works nice, but i do not need serial number to the output. Would you kindly consider it?
    Reply With QuoteRegister To Reply
  9. 04-21-2023, 03:26 PM #9
    Ariful Islam
    Ariful Islam is offline
    Forum Contributor
    Join Date
    02-16-2017
    Location
    Bangladesh
    MS-Off Ver
    2021
    Posts
    196

    Re: Copy data from one sheet to another with a blank cell available.

    It is working as expected. No serial number is required on output only. Can you help me in this matter?
    Reply With QuoteRegister To Reply
  10. 04-21-2023, 04:43 PM #10
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data from one sheet to another with a blank cell available.

    I updated the code above. I just commented out the b(g,3) line and that should do it.
    Reply With QuoteRegister To Reply
  11. 04-21-2023, 07:26 PM #11
    maniacb
    maniacb is offline
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Copy data from one sheet to another with a blank cell available.

    I was away from my computer: Here is the updated code.

    Please Login or Register  to view this content.
    Last edited by maniacb; 04-21-2023 at 07:42 PM.
    Reply With QuoteRegister To Reply
  12. 04-22-2023, 12:32 AM #12
    Ariful Islam
    Ariful Islam is offline
    Forum Contributor
    Join Date
    02-16-2017
    Location
    Bangladesh
    MS-Off Ver
    2021
    Posts
    196

    Re: Copy data from one sheet to another with a blank cell available.

    When expectations are fulfilled hundred percent, the joy cannot be explained in writing. I am grateful for the success of your efforts. Thank you very much.
    Reply With QuoteRegister To Reply
  13. 04-22-2023, 12:42 AM #13
    Ariful Islam
    Ariful Islam is offline
    Forum Contributor
    Join Date
    02-16-2017
    Location
    Bangladesh
    MS-Off Ver
    2021
    Posts
    196

    Re: Copy data from one sheet to another with a blank cell available.

    Whenever I see the respondent's name, John Topley, I understand that nothing is unsolvable. John Topley is an overwhelming respondent name. This willingness to help captivated me. I am always grateful to you. Many thanks again.
    Reply With QuoteRegister To Reply
  14. 04-22-2023, 03:48 AM #14
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,301

    Re: Copy data from one sheet to another with a blank cell available.

    @Artful: thank you very much for the rep and your kind words (which can be applied to all of the respondents on this forum who often go the "extra mile" to find solutions to the queries posed). Not least, my fellow respondents have, and continue to be, an invaluable source of learning for me.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VB code to find Blank in cell and copy row to another sheet
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2021, 05:16 PM
  2. VBA code - copy info from sheet 1 to sheet 2 but NOT COPY BLANK CELLS
    By Yossarian1980 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2017, 06:09 PM
  3. [SOLVED] VB Code to copy data in closed workbook at 1st blank cell
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2016, 01:32 AM
  4. [SOLVED] help finish/fix this code: copy non-blank cells to corresponding cell on another sheet
    By Rerock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2016, 01:38 PM
  5. [SOLVED] VBA code to copy cell values to the next blank row in another sheet (all the same row)
    By rarias in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2013, 03:08 AM
  6. How to find a value in a sheet and copy data below that value until blank cell
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-05-2012, 06:47 AM
  7. [SOLVED] Copy cell data from one w/sheet to another with unused cells left blank
    By steve@stanley in forum Excel General
    Replies: 2
    Last Post: 07-06-2012, 10:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1