loop through each row of a spreadsheet and based on the value of cells

phani2121

New Member
Joined
Jul 28, 2010
Messages
7
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> Hi all,</o:p>
<o:p> </o:p>
I want to loop through each row of a spreadsheet and based on the value of cells in column b, copy the contents of the cells in columns a,b , d, h, j, and copy it to another workbook. I have it working where I can copy the entire row but that won't work for my current project. Here is the code to get the entire row:

Sub copyRows_1()

Dim SiteCol As Range, Cell As Object

Set SiteCol = Range("B4:B700") 'Range containing pc names

For Each Cell In SiteCol

If IsEmpty(Cell) Then
Exit Sub
End If

If Cell.Value Like "PC00*" Then
Cell.EntireRow.Copy
Windows("pcMaster.xls").Activate
ActiveSheet.Range("A65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste

End If
Next
End Sub


Thanks in Advance.:cool:
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

meldoc

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
Hello

Try the below on a COPY, of your data.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> copyRows_1()<br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> wbpc <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> wspc <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Set</SPAN> wb = Workbooks("test45.xls")<br><SPAN style="color:#00007F">Set</SPAN> wbpc = Workbooks("pcMaster.xls")<br><SPAN style="color:#00007F">Set</SPAN> ws = wb.Sheets(1)<br><SPAN style="color:#00007F">Set</SPAN> wspc = wbpc.Sheets(1)<br><br>  ro = 1<br>  lr = ws.Cells(Rows.Count, "B").End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> i = 4 <SPAN style="color:#00007F">To</SPAN> lr<br>      <SPAN style="color:#00007F">If</SPAN> ws.Cells(i, "B") <SPAN style="color:#00007F">Like</SPAN> "PC00*" <SPAN style="color:#00007F">Then</SPAN><br>        ws.Cells(i, "A").Resize(, 2).Copy wspc.Cells(ro, "A")<br>        ws.Cells(i, "D").Copy wspc.Cells(ro, "D")<br>        ws.Cells(i, "H").Copy wspc.Cells(ro, "H")<br>        ws.Cells(i, "J").Copy wspc.Cells(ro, "J")<br>        ro = ro + 1<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

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
Top