Solved Excel Spread sheet tranferring selected rows

July 21, 2012 at 00:09:43
Specs: Windows 7
Hello everyone, really in need of some help!!! I have only ever used the basics on excel, spreadsheets and formulae. Ive been issued the task of tracking changes in a sheet which by simple means shows parts in a system which need changing. The single sheet contains a column which identifies the part and there are three columns which will either be left blank or one of three choices inputted. The sheet contains upto 400 rows but at any one time only about 50 of the rows will have a defect inputted in one of the three columns. The results of the sheet require printing for distribution. Im hoping there is some way i could just transfer the rows which have had defects in one of the 3 columns onto a seperate sheet to minimize the report instead of printing reams of sheets off. I know you all deal with more complex and challenging issues but any help would be greatly appreciated and hopefully set me on my way to using Excel a little bit more for its capabilities
Thankyou again

See More: Excel Spread sheet tranferring selected rows

Report •

July 21, 2012 at 07:09:40
✔ Best Answer
Let's say I start with this in Sheet 1:

     A         B         C          D
1 Part No   Defect 1  Defect 2   Defect 3
2   281			
3   275			
4   376        A		
5   254			
6   263	                 B	
7   377			
8   264                            C
9   322	                 D	
10  379        E		
11  321			
12  345			

This code will copy Rows 1, 4, 6, 8, 9 and 10 to Sheet 2:

Sub CopyDefects()
'Clear data from Sheet 2
'Copy Headings from Sheet 1 to Sheet 2
  Sheets(1).Rows(1).Copy _
'Determine last row with data in Sheet 1 Column A
  lastSrcRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows in Sheet 11
    For nxtSrcRw = 2 To lastSrcRow
'If Column B C or D contains data, find next empty row in Sheet 2
'row in Sheet 2 then copy Row from Sheet 1
     If Sheets(1).Range("B" & nxtSrcRw) <> "" _
         Or Sheets(1).Range("C" & nxtSrcRw) <> "" _
         Or Sheets(1).Range("D" & nxtSrcRw) <> "" Then
           nxtDstRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
             Sheets(1).Range("A" & nxtSrcRw).EntireRow.Copy _
               Destination:=Sheets(2).Range("A" & nxtDstRw)
     End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 22, 2012 at 02:20:53
Really appreciate the speedy reply and help.
Ive used the code and it does the job.
Is there anyway i can adjust the code ( I presume by adding or altering ) to only apply the code to a limit of rows ?
Sorry to be a pain

Thankyou one again

Report •

July 22, 2012 at 03:28:11
Been playingabout a bit, all good stuff thankyou.
Which is the best wayto apply such a macro to a button?

Report •

Related Solutions

July 22, 2012 at 08:47:45
The code below does 2 things:

1 - Determines the length of data in Column A by starting at the bottom of the column and moving up until data is found. It saves that Row number in the variable lastSrcRow.

2 - Uses the Row number (lastSrcRow) found as the upper limit of the For - Next loop

'Determine last row with data in Sheet 1 Column A
  lastSrcRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows in Sheet 1
    For nxtSrcRw = 2 To lastSrcRow

If you don't want to use all of Column A, just set your upper and lower limits "manually". This will loop through Row 5 - 20.

'Loop through rows in Sheet 1
    For nxtSrcRw = 5 To 20

As far as assigning the code to a button, a simple way is to insert a shape on your sheet. Then with the shape selected, Right Click and choose Assign Macro.

There are more elaborate methods which you could find via a Google search.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 22, 2012 at 10:17:43
Thats brilliant thankyou

Last but by no means least and I bet your fed up of me by now !!!

At the bottom of my sheet one I have a table which totals up the defects etc.

By the last help you gave me its isolated, but ideally i could do with transferring it to the sheet 2 with the list

Just say my list appeared on rows 120 to 125 inclusive

Really do appreciate your help

Thankyou once again


Report •

July 22, 2012 at 10:52:56
P.S Sorry

The tables carry over but because of the formula I get a href!
When i set the limits manually i get some of the dridlines showing even though theres no data in them on sheet 1

Report •

July 22, 2012 at 11:11:13
Without knowing more about your layout, I can't offer anything specific about transferring the table.

Generically, this code will Copy a range and PasteSpecial...Values to the designated location:

    Sheets(2).Range("F2").PasteSpecial Paste:=xlPasteValues

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 28, 2012 at 02:01:23
Thank youonce again for your help and im slowly getting a grasp of how the code works.

I substituted the manual limits as you advised but forsome reason its still going beyond and taking data over to sheet two

At the bottom of sheet one is a table showing results of the table from rows 2 to 470.

the table starts on row 475. Its a basic table with formula to total the defect. When it transfer over a ref comment appears on sheet 2, thats why i thought if i set the manual limits it would be best andjust paste the table to sheet 2.

Ive probably insertedthe limit in the wrong area in the code or not deleted something.

Onceagain im in need of expert help


Report •

Ask Question