Copy/paste to another worksheet

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:22 PM GMT

Question

You answered another persons question that im struggling with right now. The macro you gave was ...

For Each cell In Range("A2:A10")'range you want to check If cell.Value > 0 Then Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value 'line above assumes we are only doing column a, but can be 'modified if necessary to do more columns. End If Next

How would you adjust this to include columns, all the way to column "H"?

Answer

Depends a bit what you are checking - something like

Dim holdvar For Each cell In Range("A2:A10")'range you want to check holdvar=cell.value & cell.offset(0,1).value & cell.offset(0,2).value & cell.offset(0,3).value & cell.offset(0,4).value & cell.offset(0,5).value & cell.offset(0,6).value & cell.offset(0,7).value If len(holdvar) > 0 Then Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value Sheets("sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.OFFSET(0,1).Value Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.OFFSET(0,2).Value Sheets("sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.OFFSET(0,3).Value Sheets("sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.OFFSET(0,4).Value Sheets("sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.OFFSET(0,5).Value Sheets("sheet2").Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.OFFSET(0,6).Value Sheets("sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.OFFSET(0,7).Value


'I'VE MODIFIED THE ORIGINAL CODE TO KEEP THE METHOD ROUGHLY THE SAME End If Next

Advertisement

©2017 eLuminary LLC. All rights reserved.