Macro to copy non-zero items that include merged cells combined with printing.

[amandabstewart]

I am trying to use a macro to copy a portion of a worksheet to an area below the “working area” of the worksheet after data is entered. All needs to stay on the same worksheet. Within the range of A10:A36, I need the macro to copy the entry from columns A and B if the data entered in column A is not zero. Column B is a merged column of B, C, and D (necessary for formatting in the area above row 10).



I can get the macro below to work, but, it squeezes the info from Column B like "wrap text" view, rather than pasting it as it is in the working area above. How can I get it to look exactly like the column A and B that it is copying from? Ie I need the merged look from B,C, D also to show in the area below.



I also need to add to this macro to automatically print the area that the macro generates, or, to create a second macro to print this area rather than the whole worksheet.


(For non-excel translation, what I need is it to do is make an easy-to-read printed out mini-sheet of what someone has entered above. The user enters numbers in Column A (pounds of ingredients we use) of a variety of ingredients. I want to quick-print a “make this combination” mini-sheet for other employees to use, rather than having the people in our mixing area trying to read the whole darn spreadsheet. Basically, it needs to print out the recipe that someone else has entered previously using the whole list of ingredients.)


Current macro is:

Sub PrintRation()

Dim sourceRng As Range
Dim cell As Range
Dim i As Long

Set sourceRng = ActiveSheet.Range("A10:A36")
i = 46
For Each cell In sourceRng
If cell.Value > 0 Then
cell.Resize(1, 2).Copy Destination:=Range("A" & i)
i = i + 1
End If
Next cell
End Sub