VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    I have a work book which has two sheets (Input & CSM) from the Input Sheet i would like to scan column (H) starting in cell H6, when the cell is not "Blank" or "VM" i would like to copy the data in cells (H), (J), (R), (V), (AD), (AH), & (AL).

    EX:

    If H6 was not blank, Copy (H6, J6, R6, V6, AD6, AH6, AL6) The data would then be copied to Sheet(CSM) the first blank row would be A17 and i would
    want H6 to A17, J6 to B17, R6 to C17, R6 to D17, V6 to E17, V6 to F17, AD6 to G17, AD6 to H17, AH6 to I17, AH6 to J17, AL6 to k17.

    If any one can help with this i would really appreciate it.

    Mark Z.

    P.S. once i post i will attach a copy of my workbook.

  2. #2
    New Member
    Join Date
    Aug 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    I am sorry i am not able to find the attachment process, i went to reply and then clicked go advanced but i do not see the paper clip to upload my book. can someone help?

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,200
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    Try this

    Code:
    Sub CopyNoneBlank()
        Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Input")
        Set sh2 = Sheets("CSM")
        lr = sh1.Range("H" & Rows.Count).End(xlUp).Row
        sh1.Range("H5:AL" & lr).AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>VM"
        sh1.Range("H6:H" & lr & ",J6:J" & lr & ",R6:R" & lr & ",V6:V" & lr & ",AD6:AD" & lr & ",AH6:AH" & lr & ",AL6:AL" & lr).Copy
        sh2.Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues
        sh1.ShowAllData
        Application.ScreenUpdating = True
        MsgBox "Done"
    End Sub
    Regards Dante Amor

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,956
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    Quote Originally Posted by markzasz View Post
    starting in cell H6, when the cell is not "Blank" or "VM"
    Two questions...

    1) In the above part of your original post, to you mean the word "Blank" or do you mean a cell showing nothing at all?

    2) What is in Column H... constants or formulas?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Aug 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    Rick,

    There Column H contains Text or is empty, if the Cell in Column H is Blank skip or if the Cell in Column H contains the text VM skip.

  6. #6
    New Member
    Join Date
    Aug 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    DanteAmor,

    question, if i want to filter for more then two items in Column H how would you code that?

    EX: if <>, VM, KP*, KT*, SHR

    i would like to skip them.

    Mark

  7. #7
    New Member
    Join Date
    Aug 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    Dante,

    the code is very close, i am sorry for not explaining properly I will try and clarify better.

    EX of data starting in H6:AL6

    H6 J6 R6 V6 AD6 AH6 AL6
    AHIN 3000 4000 3000 4000 3000 4000

    CSM output would look like this:

    A B C D E F G H I J K
    AHIN 3000 4000 4000 3000 3000 4000 4000 3000 3000 4000
    Mark

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,956
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    Quote Originally Posted by markzasz View Post
    Dante,

    the code is very close, i am sorry for not explaining properly I will try and clarify better.

    EX of data starting in H6:AL6

    H6 J6 R6 V6 AD6 AH6 AL6
    AHIN 3000 4000 3000 4000 3000 4000

    CSM output would look like this:

    A B C D E F G H I J K
    AHIN 3000 4000 4000 3000 3000 4000 4000 3000 3000 4000
    Mark
    Can I make a suggestion to you? When you give an example where apparently some values are repeated after processing, do not use the same number over again in your original data since we cannot always tell which repeated data goes where. Try changing your "original" starting data to this...

    H6 J6 R6 V6 AD6 AH6 AL6
    AHIN 1000 2000 3000 4000 5000 6000

    and then show us how that would look on the CSM sheet.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    New Member
    Join Date
    Aug 2015
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    Rick,

    Changing input data is not an option as these are UCB's that are input from other processes and the CSM output will be used to replicate storage volumes to three different sites.

    Mark

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,956
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA CopyNoneBlank data from Multiple Cells from One Sheet to another.

    Quote Originally Posted by markzasz View Post
    Rick,

    Changing input data is not an option as these are UCB's that are input from other processes and the CSM output will be used to replicate storage volumes to three different sites.
    I am not asking you to physically change your actual data, rather just make believe what I posted was actual data and show us where each cells value would go when duplicated. As I look at the example you posted, it is not entirely clear which 3000 goes where on the CSM sheet nor which 4000 goes where either. We can make a guess, but if you use the numbers I suggested and show how they would move to the CSM sheet, it would be much clearer. Remember, you are asking for help from people who know nothing about your data or what you are trying to do with it... we only know what you tell us so you should make any examples as non-ambiguous as possible for us.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •