vba to copy concatenated cells leaving out the blanks Excel 2010

[Lsaffell]

I have a spreadsheet that uses a concatenated formula to copy values from column A to column B that looks like this: =CONCATENATE("(16)", " ", Master!A16)

They have asked me to write a macro to move the value from column B to column B in another worksheet. Here is my Vb:
Sub Macro1()
'
' Macro1 Macro
'
'
Application.ScreenUpdating = False
Worksheets("Master").Range("B1:B97").Copy
Worksheets("3500_Template").Range("B7:B102").PasteSpecial xlValue
SkipBlanks = True
Application.ScreenUpdating = True

End Sub


Everything works except when column A on original sheet is Blank, the concatenante formula still runs putting the first value in the field of column B, in this case (34), (35), etc. I do not want to copy those values to the second worksheet. I would like for the cells to be null. This is the first time I have tried to write a macro and I just started working with excel so forgive me if this does not make sense. Any help would be greatly appreciated![TABLE="width: 224"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

First off. You will never have to type CONCATENATE again if you don't want to. Excel has a operator & that does the same thing. ="(16) " & Master!A16
will accomplish the same thing your formula does.

So I have two questions, why are you hardcoding the number in the CONCATENATE function when it obviously changes? Why don't you just incorporate an IF in your original formula to not display anything if the value in Column A is not there:

=IF(MASTER!A16="","","(16) "&Master!A16)

 

[circledchicken]

Hi and welcome to the forum,

They have asked me to write a macro to move the value from column B to column B in another worksheet. Here is my Vb:

Who's they?

Can you perhaps change your original formula to something like:

=IF(Master!A16 = "", "", CONCATENATE("(16)", " ", Master!A16))

or

=IF(Master!A16 = "", "", "(16) " & Master!A16)

You can probably replace the hardcoded 16 as well, but we need a little more context behind what you are doing.

In your VBA, the size of the range you are copying from is larger than the range you are pasting to? Is that just a typo?

 

[Lsaffell]

I didn't code the Concatenate, someone else did. It is hard coded because they are copying the values and then inserting a blank line or two every seven to 14 cells to be used as a template for a Biology instrument where they have to include a validation tube in the tray. I'm sure I can change it but I wasn't sure how to go about doing that without messing up their template. In answer to your first question, I don't know why the person who did it wrote it that way. In answer to your second question I can incorporate an IF statement but I clearly don't know what I'm doing which is why i'm on this forum asking for help. I think I just need to go to the Library and check out some EXCEL and VBA books. Thanks anyway for your input.

First off. You will never have to type CONCATENATE again if you don't want to. Excel has a operator & that does the same thing. ="(16) " & Master!A16
will accomplish the same thing your formula does.

So I have two questions, why are you hardcoding the number in the CONCATENATE function when it obviously changes? Why don't you just incorporate an IF in your original formula to not display anything if the value in Column A is not there:

=IF(MASTER!A16="","","(16) "&Master!A16)

 

[Lsaffell]

Hi, They are the people I work with who use this spreadsheet on a daily basis to run fluids through a Scientific Instrument. They are using the 3500 Template sheet to pull information into the instrument for identifying blood, etc. The range is different and they coded the concatenate so they could enter the values and then copy those values to another column where they will insert a few blank rows for the validation kit on the instrument. That is why the range is different on the one I copied to. With that being said if they don't fill in every cell in A for which they have a Concatenate formula set up for in Column B then they end up with the number which is in parenthesis in the cell. That number corresponds with a place on a plate where they put the test tube. I want to copy the values from Column B minus the "empty" cells to the template column B that they use to do the run on the Instrument. They asked me to automate that step so I did a Macro for them. Very basic because I have just a very basic understanding of Macros. I hope this makes sense. Hard to describe. Thanks for looking and responding. If I need to change the original formula's they created I can I was just trying to do something quick and easy for them.