cut and paste into new sheet based cell value

October 6, 2010 at 22:50:13
Specs: Windows 7
I need to create a macro in excel 2007 which cuts the entire row and paste into another workbook based on a set of criteria.
The formula looks up sheet2 collum A:A if the attribute can be found in sheet1 A:A then copy the entire row to sheet 3, repeat this process until all you reach a blank in sheet2 A:A

I hope I have provided enough infromation



See More: cut and paste into new sheet based cell value

Report •

October 7, 2010 at 02:04:37
How about this:

Option Explicit
Sub CopyToSheet3()
Dim lastSht2_Row, sht2_Row, nxtSht3_Row As Integer
Dim c As Range
'Determine length of data in Sheet 2 Column A
 lastSht2_Row = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
'Loop through Sheet 2 Column A
  For sht2_Row = 1 To lastSht2_Row
'Search for data from Sheet 2 Column A in Sheet 1 Column A
   With Worksheets(1).Range("A:A")
    Set c = .Find(Sheets(2).Cells(sht2_Row, 1), LookIn:=xlValues)
''If found, copy entire row to Sheet 3
     If Not c Is Nothing Then
'Incremement Row counter for Sheet 3
      nxtSht3_Row = Sheets(3).Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy/Paste Row
        Sheets(1).Cells(c.Row, 1).EntireRow.Copy _
         Destination:=Sheets(3).Cells(nxtSht3_Row, 1)
     End If
   End With
End Sub

Report •

October 7, 2010 at 15:38:10
Hi DerbyDad03,

Thanks for looking at this!

The code runs, it collects the infromation from sheet2 A:A and copies it to sheet 3, but only copies one line item thie first instance of the item

Can you please modify to achieve the following

Using the same fields infromation in sheet 1 A:A
List of attributes to be cut in sheet 2 A:A and destination in sheet 3

1. Cut all instances (entire row) from sheet 1 and paste into sheet 3
2. delete the blanks.

I have been manually doing this by filtering on the attribute (sheet 1 A:A) and cutting and pasting to a new sheet.

thanks again for looking at this


Report •

October 7, 2010 at 15:40:44
to clarify point 2: delete the blank rows (the rows which have been cut) and fill up

Thaks again


Report •

Related Solutions

October 7, 2010 at 16:46:10
Let's review what you asked for in your OP:

if the attribute can be found in sheet1 A:A then copy the entire row to sheet 3

1 - Do you see anything in that statement that give any indication that "the attribute" will be found more than once in Sheet1?

2 - You said "copy the entire row". Now you are asking that the row be cut and the blank row deleted.

Even if we give you the benefit of the doubt and call number 1 a mere misunderstanding, there can be no misunderstanding between the request to copy the row vs. the request to cut the row.

You are are now going to be subject to my canned lecture about changing the request mid-thread:

Don't take this the wrong way, but we often get questions in this forum where requirements come in drips and draps.

"I need a macro to this"

"Thanks, now can you make it do this too?"

"Hey, that's great. Now, how do I add this?"

That gets a bit annoying since we have to go back and modify the code, sometimes even rewriting it completely so we don't end up with inefficient, cobbled together code.

In addition, we (well, at least I) don't save every workbook that we set up to test our code. When additional requirements come along later, we (I) usually have to copy the code from the forum, set up the workbook with data in the correct columns, accounting for the new requirements, etc.

Since I don't want to just add lines to the code and post it without testing it, I have to go through extra (repeated) work to make sure it meets the new set of requirements.

Think about managing a project and asking someone to quote on it or even implement it. Once you start adding requirements after the initial work has begun, things get messy - and usually more expensive.

That said, I'll take a look at your new requirements and see what I can do - after I set up a new workbook, with data in the correct columns and copy the original code to the VBA editor.

Report •

Ask Question