copy row from one excel sheet to another based on 2 criteria inputs

excel copy rows from one sheet to another based on criteria
macro to copy data from one sheet to another based on criteria
excel vba copy rows to another worksheet based on multiple criteria
excel copy row to another sheet based on cell value
excel formula copy rows based on criteria
excel pull data from another sheet based on date
excel copy data from one cell to another if a condition is met
automatically move entire rows from one worksheet into another worksheet in the same workbook

I have an excel sheet where the first sheet contains all of the master data which will be around 500 rows and goes across to column R, this sheet is called Overall Sheet. Column O includes a month, and Column P includes a year.

I have another sheet where I would like the data to be copied this is called "Forecast Month". at the top in B1, the month that I would like to be copied in is selected, and in D1, the year is selected. I would like the button to read these two cells and copy in the data from "overall sheet" based on this.

I have written this code as shown below, but for some reason the data is entered into "forecast month" 10 times before adding the next one (also 10 times). I should only have 3 pieces of data in this sheet but instead there is 30, 10 for each.

Also the top 3 lines on each sheet have headings so the data should start writing on row 4 (which it does)

Please can anybody help??

Private Sub CommandButton1_Click()
    Dim month As String
    Dim year As String

    Dim c As Range
    Dim d As Range

    Dim k As Integer
    Dim source As Worksheet
    Dim targetforecastmonth As Worksheet

    'change worksheet designations as needed
    Set source = ActiveWorkbook.Worksheets("Overall Sheet")
    Set targetforecastmonth = ActiveWorkbook.Worksheets("Forecast Month")

    targetforecastmonth.Range("A4:Z1000").Clear

    month = ActiveWorkbook.Worksheets("Forecast Month").Range("B1")
    year = ActiveWorkbook.Worksheets("Forecast Month").Range("D1")

    k = 4

    For Each c In source.Range("O4:O1000")
        For Each d In source.Range("P4:P1000")
            If c = month And d = year Then
                source.Rows(c.Row).Copy targetforecastmonth.Rows(k)
                k = k + 1
            End If
        Next d
    Next c
End Sub

Try this, I hope this would help.

Private Sub CommandButton1_Click()
Dim month As String
Dim year As String

Dim c As Range

Dim k As Integer
Dim source As Worksheet
Dim targetforecastmonth As Worksheet

'change worksheet designations as needed
Set source = ActiveWorkbook.Worksheets("Overall Sheet")
Set targetforecastmonth = ActiveWorkbook.Worksheets("Forecast Month")

targetforecastmonth.Range("A4:Z1000").Clear

month = ActiveWorkbook.Worksheets("Forecast Month").Range("B1")
year = ActiveWorkbook.Worksheets("Forecast Month").Range("D1")

k = 4

For Each c In source.Range("O4:O1000")
If c = month And source.Cells(c.Row, 16).Value = year Then
source.Rows(c.Row).Copy targetforecastmonth.Rows(k)
k = k + 1
End If
Next c

End Sub

Copy data from one sheet to another if criteria met, I want to automatically copy data from one worksheet to another based on criteria being met. Excel; /; Other/unknown; /; Office 2007 with data for items (key col) eg: chain, wire, etc running in row 2 down cater for the complete list of items (​just change the input in A1 in the copies, a one-off exercise). I'm trying to use advanced INDEX and MATCH functions to pull entire rows out of one sheet and move to another automatically. For instance, if I were to have 3 sheets open, let's say I copy data from an Internet database, put it in Excel format, copy it to Sheet 2.

It seems wrong logic there is. I suppose you need Expl.: O8, P8 matches B1, D1 So you need only one cycle:

For Each c In source.Range("O4:O1000")
d = source.Range("P" & k)
If c = month And d = year Then
    source.Rows(c.Row).Copy targetforecastmonth.Rows(k)
End If
k = k + 1
Next c

Copy Data to Other Sheets' Columns Based on Criteria, I have one Orders sheet and 12 Month sheets (Jan, Feb, Mar, etc). The Orders Copy Data to Other Sheets' Columns Based on Criteria The column headers on the Orders sheet are different from those on the month sheets. Now select all cells in row 2 and drag them down till out of your ordes in the month, better more. I have another sheet where I would like the data to be copied this is called "Forecast Month". at the top in B1, the month that I would like to be copied in is selected, and in D1, the year is selected. I would like the button to read these two cells and copy in the data from "overall sheet" based on this.

You have a nested For Each loop, meaning that you take cell "O4" then loop through cells "P4:P1000" before moving on to cell "O5" and looping through cells "P4:P1000" again and so forth... If for example the cell value of "O4" satisfies the month criteria, then every time the loop through column P finds a cell that satisfies the year criteria, row number 4 will be copied and pasted. Try this instead:

Private Sub CommandButton1_Click()
    Dim month As String
    Dim year As String

    Dim c As Range
    Dim d As Range
    Dim x As Long

    Dim k As Integer
    Dim source As Worksheet
    Dim targetforecastmonth As Worksheet

    'change worksheet designations as needed
    Set source = ActiveWorkbook.Worksheets("Overall Sheet")
    Set targetforecastmonth = ActiveWorkbook.Worksheets("Forecast Month")

    targetforecastmonth.Range("A4:Z1000").Clear

    month = ActiveWorkbook.Worksheets("Forecast Month").Range("B1")
    year = ActiveWorkbook.Worksheets("Forecast Month").Range("D1")

    k = 4
    x = 4

    For Each c In source.Range("O4:O1000")
        Set d = source.Range("P" & x)
        If c.Value = month And d.Value = year Then
            source.Rows(c.Row).Copy targetforecastmonth.Rows(k)
            k = k + 1
        End If
    x = x + 1
    Next c
End Sub

Match two criteria and return multiple records, Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. $C$3:$C$19)​, ROW($B$3:$D$19)-MIN(ROW($B$3:$D$19))+1), ROW(A1)), COLUMN(A1)) Match two criteria and return multiple records [Excel defined Table] On the sheet, I have a box for user input as to which frame they'd like to use. The line makes use of 2 embedded cell objects within a range object, to limit the column of a targeted copy row from column A to F. ( Hint: In Excel, column A takes a numeric column value of 1, while column F takes a numeric column value of 6). After that, a copy method is introduced to copy the current row under looping.

Copying Rows between Worksheets Based on a , Copying Rows between Worksheets Based on a Text Value excelfox.com/​forum/forumdisplay.php/2-Excel-Help I now want a specific cell B6 in the target worksheet to provide the search criteria for the search in the source sheet. and I want to be able to go to another sheet and copy paste text and  Excel Functions don’t have a “Push” feature, just a “Pull” so as long as the data remains on the Master sheet then it’s straightforward to pull it to a tab for each Client.

How to Copy Rows to Other Worksheets Using Selection Criteria , How to Copy Rows to Other Worksheets Using Selection Criteria end up needing to copy row data from one worksheet to another worksheet. Suppose the user has a workbook which contains 5 worksheets with After the user inputs the criterion worksheet names manually under 2, Application. Sometimes you need to copy cells that meeting certain criteria. And here we will introduce 2 useful methods to copy the target cells. In an Excel worksheet, there will certainly be a lot of data and information. However, those cells contain values that meet certain criterion will separate in different range.

How to move entire row to another sheet based on cell value in Excel?, Press the F5 key to run the code, then the row which meet the criteria in Sheet1 VBA code 2: Copy entire row to another sheet based on cell value select all rows based on a certain cell value or different cell values in a worksheet, the last row in the second sheet, it only checks for inputs in columns A - E. So if I have a  Copy rows from multiple worksheets based on criteria into a new sheet with VBA code. The following VBA code can help you to copy specific rows from all worksheets within the workbook based on a certain condition into a new worksheet. Please do as this: 1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. 2.

Comments
  • Please provide some comments and explanation as to why what you have posted is an answer.
  • I'm glad it worked :) You were looping the rows multiple times and that caused the error. Instead of a single loop you used a nested one, and that's why you had multiple lines printed.