Copy Entire Rows to Another Spreadsheet Based on Cell Criteria
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
Rate This Tips:
Incoming excel search terms
excel how to make one name in a spreadsheet reference in another tab,excel copy rows based on criteria,macro to copy rows based on criteria,vba copy rows to new sheet,,vba copy row to another sheet,excel vba copy row,excel macro copy row based on cell value,copy row based on cell value,copy rows based on a condition,excel copy row to new sheet,excel vba copy rows,vba copy row,vba copy row from one sheet to another,vba excel copy rows between sheets,copy complete row to other worksheet excel macro,vba copy rows based on 2 conditions,copy a spreadsheet into a new tab in excel,copy row excel vba,copy row from one sheet to another vba,copy rows based on criteria,copy rows from one table to another excel,copy rows to a new sheet if cell criteria are met,excel copy row based on cell,excel copy row based on cell value,excel copy row based on condition,excel copy row to another sheet,excel copy row to new workbook,excel formula to move row to another sheet when criteria is met,excel macro that copy data from multiple closed worksheet to another worksheet,excel vba copy and paste based on value,excel vba copy entire column,excel vba move row based on criteria,extract data from one worksheet to another worksheet based on a criteria,how can you populate another worksheet in excel if a condition is met on another tab?,if you copy and paste a formula from excel to another spreadsheet it shows value,macro to transfer data from one spreadsheet to another spreadsheet in excel,transfer rows based on values vb excel to new sheet,vba copy worksheet to another workbook,vba excel copy row,0h,5 00 spreadsheet,automate copy data from one excel to another,close spreadsheet based on cell value,code to copy rows from one excel to a new excel workbook using vb,conditions in excel from one worksheet to another,copy a cell from one sheet if a criteria is met to another sheet,copy a part of row from a spreadsheet formula,copy cell if criteria is met,copy cells-of-rows based on two conditions
Related Excel Tips
Comments
4 Comments on Copy Entire Rows to Another Spreadsheet Based on Cell Criteria
-
Larry on
Tue, 29th Sep 2009 7:26 pm
-
RENEE on
Thu, 22nd Oct 2009 7:07 pm
-
RENEE on
Thu, 22nd Oct 2009 7:18 pm
-
vp shokeen on
Thu, 11th Feb 2010 11:32 am
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
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…
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…
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..














