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

markzasz

New Member
Joined
Aug 12, 2015
Messages
27
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

markzasz

New Member
Joined
Aug 12, 2015
Messages
27
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,153
Office Version
2007
Platform
Windows
Try this

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,035
Office Version
2010
Platform
Windows
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

New Member
Joined
Aug 12, 2015
Messages
27
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

New Member
Joined
Aug 12, 2015
Messages
27
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

New Member
Joined
Aug 12, 2015
Messages
27
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,035
Office Version
2010
Platform
Windows
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

New Member
Joined
Aug 12, 2015
Messages
27
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,035
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,261
Messages
5,485,734
Members
407,511
Latest member
Arunabh

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top