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

[markzasz]

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.

 

[markzasz]

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?

 

[DanteAmor]

Try this

Sub CopyNoneBlank()
    Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
    Application.ScreenUpdating = False
    Set sh1 = Sheets("[COLOR=#0000ff]Input[/COLOR]")
    Set sh2 = Sheets("[COLOR=#0000ff]CSM[/COLOR]")
    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

 

[Rick Rothstein]

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?

 

[markzasz]

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.

 

[markzasz]

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

 

[markzasz]

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

 

[Rick Rothstein]

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.

 

[markzasz]

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

 

[Rick Rothstein]

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.