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

markzasz

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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top