Excel: Need to copy and paste cells depending on non-blank status of cell values in one row in range

Question

Macro newbie here....

I am trying to paste certain cells within a range from one worksheet to another based on the contents of cells in a particular row. For instance, within range B5:B100, I want to copy and paste the B cells to another worksheet -- and their companion row cells in columns J and M -- when the B cell of the row in question is non-blank. And instead of having blank columns in the worksheet2, I need the results to paste neatly into columns A,B,C).

For example, let's say there are only two non-blank cells in the worksheet1 range B5:B100 - cells B26 and B78. Running the range macro would then copy B26, J26, M26 and B78, J78, and M78 then paste them into the second worksheet starting at A2 (to allow for header row) and without blank rows (so B26 to A2, J26 to B2, M26 to C2 and B78 to A3, J78 to B3, and M78 to C3).

I was able to do a non-blank copy and paste of jsut the b column values but lost as to picking up the other needed cells for each row.

Thanks!

Answer 1

I would suggest:

• Find the item using Cells.Find
• When you find the item, you can get the row/column of the cell. Then on the new sheet, using a cell reference, you can say something like

Sheet2.Cells(curRow,"A").Value = Sheet1.Cells(foundCellRow,B).Value

• For the adjacent columns, you can say

Sheet2.Cells(curRow,"B").Value = Sheet1.Cells(foundCellRow,"J").Value

I hope this helps