Automatically Copy But Skip blank cell of a column to another sheet.

  • Hi,


    I think it's a simple thing but I am not able to do this. Hope someone help.


    I have a datasheet name Source where a column data starts AI9 to AI1200 mixed with blanks and nonblank cells,

    I want to copy the data (Skip the blank cell which has a formula) to another sheet name Destination.

    The thing is if anything changes to AI9 to A1200, It reflects the destination sheet Automatically.

    I mean suppose previously a blank cell now has data it updates to the destination sheet automatically.


    I am trying to use the below code but nothing happens


    Thanks in Advance

  • Try this, the code needs to be placed in the Worksheet Object Module for Sheet4

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks, Kjbox,


    Maybe I make some mistake, Will you take a look again.

    Sheet 4 is the source sheet & sheet 24 is the destination sheet.

    How does data go from 4 to 24? in the above code. Please explain for better understanding.

    In the destination sheet24 data copied starts to D4:D


    Thank You.

  • Try this

    The code loads the data from Columns AH & AI of the Source Sheet into array x. Code then loops through array x and builds array y if the second dimension of x is not blank. The code then places the contents of array y onto the Destination sheet.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Change

    Code
    1. If x(i, 1) <> "" Then

    To

    Code
    1. If x(i, 2) <> "" Then

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • The data is in columns D and E. The reason column D is empty is because your sample file has no data in Source Sheet Column AH.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi, Kjbox it's my mistake.

    In my sample file, 34 is there to count the column number.


    But by mistake, It gives my calculation further easy.


    can I get I9:I data instead of AH9:AH by changing the below


    Code
    1. x = .Range(.Cells(9, 9), .Cells(lRow, 35))


    In the destination sheet First comes the AI then AH data.



    In what particular field do I need to change. Suggestion, please?

  • It is very difficult when your sample files bare no resemblance to your actual file, and you are unable to adapt code that works on your sample file to suit your actual file.


    You really need to attach your actual file and explain exactly what it is you need, then those trying to help you will not waste time.


    All I could go on was the code you had, which copied Column AH of the Source sheet to Column A of the Destination sheet, I thought you wanted to change that to column D.


    Now you want to use Columns I and J in the source file, is that correct?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi, Kjbox.


    At first, I am really sorry for misleading you and for wasting your valuable time.

    I also thank you for giving such time to making complex things easy. I hope you don't misjudge me because I have very poor knowledge of VBA. I am trying to understand the logic behind it. And I also careful about my future post.



    Here I attached a Shadow File AMC Record.xlsm removing some sophisticated Data


    All my requirements have been elaborate in details in the file.

    Hope you help me.


    Thanks and Best Regards.

  • Try this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome.


    Do you need it to work automatically? If so what will the trigger be to run the macro? Alternatively you could add a button to the Source sheet and assign the macro to it.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I Figure it out.


    If this paste in Source file code module

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)

    Then it runs automatically.


    In destination Sheet

    Code
    1. Sheet24.Range("K7").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))

    Agreement Number and Contract Amount are sides by side. Can the Contract amount move two columns back? I mean the "N" column instead of the "L" column.


    If so what changes should I make in the above code?


    Thanks, Kjbox, I really appreciate your works.


    Best regards.

  • Like this?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.