Copy Entire Rows to Another Spreadsheet Based on Cell Criteria

September 29, 2009 by vba excel
Filed under: Excel Spreadsheet, Excel Vba 
Sponsor

Excel Vba Copy Entire Rows to Another Spreadsheet Based on Cell Criteria [Q]

I am looking for excel vba codes to copy rows based on partial cell content. I have an excel spreadsheet called “main.xls” from which I would like to copy data to another excel spreadsheet when certain criteria are met.

Column I have following data.
TML123
TML702
TML4568
TML956
FTF987
FTF0956
FTF687
TML257

I would like the macro to copy rows that have TML prefix to another workbook (After.xls) and paste under a tab named TML. The macro should also copy rows that have FTF prefix to the same workbook (After.xls) and paste under a tab named FTF.

Excel Vba Copy Entire Rows to Another Spreadsheet Based on Cell Criteria [A]

As a starter for 10 …lets use the filter to make it easier. this extracst
the data to two new worksheets. All you need to do is copy the data.

Option Explicit
Sub FilterData()
Extract “TML”
Extract “FTF”
End Sub
Sub Extract(what As String)
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Range(”A1″) = “AAA”
ws.Range(”D1″) = “AAA”
ws.Range(”D2″) = what & “*”

Sheets(”Sheet1″).Range(”A1:A1000″).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws.Range(”D1:D2″), CopyToRange:=ws.Range(”A1″),
Unique:=False

‘To DO
‘ Copy the data from ws to wherever you want

End Sub

To automate the whole excel process and that includes the
coping and pasting to new workbook. The main.xls file is open and the excel macro is located in after.xls :

Try the below macro from Main xls activesheet

Sub Macro()

Dim wb As Workbook, lngRow As Long, lngNextRow As Long
Set wb = Workbooks(”after.xls”)

For lngRow = 1 To ActiveSheet.Cells(Rows.Count, “I”).End(xlUp).Row
If Range(”I” & lngRow) Like “TML*” Or Range(”I” & lngRow) Like “FTF*” Then
lngNextRow = wb.Worksheets(CStr(Left(Range(”I” & lngRow), 3))).Cells( _
Rows.Count, “I”).End(xlUp).Row + 1
Rows(lngRow).Copy _
wb.Worksheets(CStr(Left(Range(”I” & lngRow), 3))).Rows(lngNextRow)
End If
Next

End Sub

  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • E-mail this story to a friend!
  • Live
  • MisterWong
  • Propeller
  • StumbleUpon
  • Technorati
  • TwitThis
  • YahooMyWeb
  • Print this article!

Rate This Tips:

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 3.00 out of 5)
Loading ... Loading ...


Incoming excel search terms

excel copy rows based on criteria,excel how to make one name in a spreadsheet reference in another tab,macro to copy rows based on criteria,excel macro copy row based on cell value,mss,,excel vba copy row from one sheet to another,excel vba copy row to another worksheet,vba copy row to another sheet,vba copy rows to new sheet,copy rows based on a condition,excel vba copy row,vba copy row,copy row based on cell value,excel copy row based on condition,excel copy row to new sheet,excel vba copy rows,vba copy row from one sheet to another,vba excel copy rows between sheets,spreadsheet copy row,0,copy entire row vba,excel copy row based on cell,excel copy row based on cell value,excel copy row to new workbook,vba copy worksheet to another workbook,copy complete row to other worksheet excel macro,copy row excel vba,copy rows based on criteria,copy rows from one table to another excel,excel copy row to another sheet,excel copy rows to new sheet,excel macro copy row to another sheet,excel macro copy worksheet to another workbook,excel vba move row to new sheet based on criteria,vba copy rows based on 2 conditions,vba excel copy row,vba excel copy row to another worksheet,0h,copy a spreadsheet into a new tab in excel,copy entire row to another sheet,copy excel rows to another worksheet,copy range based on cell value,copy row from one sheet to another vba,copy row to another worksheet,copy rows based on condition,copy rows to a new sheet if cell criteria are met,copy selected columns to another sheet using vba,excel copy a range to another worksheet,excel copy worksheet to another workbook



Related Excel Tips

Tags: , , , ,

Comments

4 Comments on Copy Entire Rows to Another Spreadsheet Based on Cell Criteria

  1. Larry on Tue, 29th Sep 2009 7:26 pm

    2. Similar question.

    I am trying to create a Grade Book for my Wife (a Teacher, elementary grade).
    Most of the grade books I have found are very low level and limited data.

    What I need to do is,
    Copy rows and cells of data to New Sheets Created.
    On the main Sheet, a button to create a new sheet.
    Each sheet is to be named as per the project/subject matter, and the data is for weekly based tests and assignments.

    The info to copy is just the student name/info and teacher data, as well as the weekly info as headers in the columns.

    I wish for this option to happen when the button is clicked (either run a Macro or based on VBA coding). Once the teacher inputs grade points then code runs to amortize the totals to the main page for general info that can be printed as data info for the parents.

    Thanks
    Larry

  2. RENEE on Thu, 22nd Oct 2009 7:07 pm

    3. I AM NEW TO THE EXCEL SPREADSHEETS. MY EMPLOYER, HOWEVER REQUIRES IT FOR CERTAN JOBS. FOR INSTNACE ONE THAT OFTEN STUMPS ME IS A SPREADSHEET THAT WE USE FOR MONEY AMOUNTS WITH VISA, MASTERCARDS ETC.. CHECKS, AND CASH. I CANT SEEM TO COPY A NEW FILE FROM THE BLUE PRINT. PLEASE HELP ME FIGURE THIS OUT…

  3. RENEE on Thu, 22nd Oct 2009 7:18 pm

    4. SORRY FORGOT TO MENTION THAT WE WORK FROM ONE DAY TO THE NEXT 10/22/2009 1023/2009, AND SO ON ALL I WANT TO DO IS COPY THE FROM THE ORIGINAL WITHOUT BALANCE…

  4. vp shokeen on Thu, 11th Feb 2010 11:32 am

    5. sir,
    i want to copy a index no with a range in the row from sheet2 to between the index no. less then and greater then in sheet1 rest of the code is working to my needs.
    code attached as under
    pl.help me

    shokeen

    Option Explicit
    Sub Macro1()
    ‘
    ‘ Macro1 Macro
    ‘ Macro recorded 09/02/2010 by OIC
    ‘ Dim Rng1A As String

    Dim k, j, l, i
    Dim RNG As Range
    Dim tt
    Dim Rng2 As String
    Dim Rng3A As String
    Dim Rng3B As String
    Dim Rng1A As String
    Dim Mach As String
    Dim msg
    Dim msgcounter(50)
    Dim CCC As String
    Dim BB As String
    Dim resp

    Dim ro As Integer

    Do
    ro = ro + 1
    tt = Sheet3.Cells(ro, 1)
    Loop Until tt = “”
    ro = ro - 1
    Sheets(”SHEET1″).Select
    Columns(”C:C”).Select
    Selection.Insert Shift:=xlToRight

    For k = 1 To 25

    For i = 1 To 25
    Mach = “N”
    If Sheet2.Cells(k, 1) “” Then If Sheet1.Cells(i, 1) “” Then If Sheet2.Cells(k, 1) = Sheet1.Cells(i, 1) Then Mach = “Y”

    If Mach = “Y” Then
    Beep
    ro = ro + 1

    Rng1A = Cells(k, 4).Address
    Rng3A = Cells(i, 5).Address
    Rng3B = Cells(i, 2).Address
    Sheets(”SHEET1″).Range(Rng3A) = Sheets(”Sheet2″).Range(Rng1A).Value
    Sheets(”SHEET1″).Range(Rng3B) = Sheets(”sheet2″).Cells(k, 5).Value + (Cells(i, 2))
    Exit For
    End If

    Next
    msg = “dealer code not found ” & Sheet2.Cells(k, 1) & ” ROW NO.” & k

    If i = 26 Then
    resp = MsgBox(msg, vbYesNo)
    ‘ If RESP = vbYes Then

    For l = 1 To 25
    If Sheet2.Cells(k, 1) < Sheet1.Cells(l, 1) Then

    Rows(l - 1 & “.” & l - 1).Select
    Selection.Insert Shift:=xlDown
    With Sheet2
    ‘ Sheets(”Sheet2″).Select
    BB = Sheet2.Cells(k, 1).Address ‘ & “:” & Sheet2.Cells(K, 10).Address
    Range(BB).Select
    Selection.EntireRow.Copy
    End With
    Sheets(”Sheet1″).Select
    CCC = Cells(l - 1, 1).Address & “:” & Cells(l - 1, 4).Address
    ‘ Range(CCC).Select
    Sheets(”SHEET1″).Range(CCC) = Sheets(”sheet2″).Range(BB)
    ‘ Selection.XLPaste
    ‘ Selection.Paste
    ‘Selection.xlPaste
    Exit For
    End If

    Next
    ‘ End If
    j = j + 1
    msgcounter(j) = msg

    End If

    Next
    For k = 1 To j
    MsgBox msgcounter(j) & ” WANT TO ADD ROW NO.”, vbYesNo
    If vbYes Then
    Beep
    End If
    Next
    End Sub

Have another excel answer or questions for this problem ?
Feel free to post it here..