Copy Data from One Sheet to the Next Sheet Based on Criteria.

[SouthernGent0327]

Good Evening,

This workbook is setup for a userform to enter PO information so it can be added to a dynamic PO log. Once logged the user will close the userform and select a comboBox value Yes or No to indicate whether or not this PO should be deducted from the monthly budget. That being said, if the user selects NO the entire row should be copied to the very next page in the workbook, which is also the very next month. This should happen via a Worksheet Selection_Change Event only if the comboBox value = No, if the user selects Yes other formulas will add the value to the total deductions so it should essentially be ignored by the loop. Also, the pages in this workbook are exactly the same, so the range for the second month will be the same as the first month C14:H14, which again dynamically updates depending on how many NO values are selected. I am having alot of trouble getting the code to find only NO values and dynamically copy the row C14:H14 to the very next available row in the next worksheet. Please see the attached code I have written

Sub Transfer()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lRow1 As Long
Dim lRow2 As Long
Dim i As Long
Dim Crit As Range

Set ws1 = ActiveSheet
Set ws2 = ActiveSheet.Next

lRow1 = ws1.Range("J" & Rows.Count).End(xlUp).Row

For i = 14 To lRow1

If ws1.Cells(i, 10).Value = "No" Then

ws1.Range("C" & i & ":H" & lRow1).Copy

ws2.Activate

lRow2 = ws2.Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Row

ws2.Range("C14:H" & lRow2).PasteSpecial Paste:=xlPasteValues

End If

Next i

End Sub

This code only copies the last two data points and virtually ignores the condition. It will copy Yes and No if they are mixed throughout the data set and I am not sure why this is happening. Please help me solve this problem. Thank you in advance for the help.

 

[Zot]

I have no idea how this code relates to the ComboBox you talked about. I think your code should be

Sub Transfer()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lRow1 As Long
Dim lRow2 As Long
Dim i As Long
Dim Crit As Range

Set ws1 = ActiveSheet
Set ws2 = ActiveSheet.Next

lRow1 = ws1.Range("J" & Rows.Count).End(xlUp).Row
For i = 14 To lRow1
    If ws1.Cells(i, 10).Value = "No" Then
        ws1.Range("C" & i, "H" & i).Copy
        lRow2 = ws2.Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Row
        ws2.Range("C" & lRow2).PasteSpecial (xlPasteValues)
    End If
Next i

End Sub

 

[SouthernGent0327]

I have no idea how this code relates to the ComboBox you talked about. I think your code should be

Sub Transfer()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lRow1 As Long
Dim lRow2 As Long
Dim i As Long
Dim Crit As Range

Set ws1 = ActiveSheet
Set ws2 = ActiveSheet.Next

lRow1 = ws1.Range("J" & Rows.Count).End(xlUp).Row
For i = 14 To lRow1
    If ws1.Cells(i, 10).Value = "No" Then
        ws1.Range("C" & i, "H" & i).Copy
        lRow2 = ws2.Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Row
        ws2.Range("C" & lRow2).PasteSpecial (xlPasteValues)
    End If
Next i

End Sub

Thank you so much for your help. To answer your questions regarding the Combobox, it's actually a data validation dropdown with only Yes or No as possible selections. The code you provided works great, but I have a hidden sheet to store these values, as well as values that update a dynamic dropdown list for my userform. The rows are being copied to the hidden sheet. Can you help me add it to the next visible sheet. I have tried adding ws2.Visible = True to the If statement, but it doesn't seem to work. I am writing the code this way because when the workbook opens for the first time it copies the Master sheet for all 12 months of a fiscal year, which is why I am using ActiveSheet and ActiveSheet.Next.

 

[Zot]

If you are trying to write in all worksheet, then why not loop through all sheets in the workbook?

Private Sub Workbook_Open()

Dim ws As Worksheet, wsMaster

Set wsMaster = ActiveWorkbook.Sheets("MasterSheet") ' replace the name of master sheet here.

For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = wsMaster.Name Then
    '<your code here>
    End If
Next ws
    
End Sub

if you want to loop through only visible sheets

Private Sub Workbook_Open()

Dim ws As Worksheet, wsMaster

Set wsMaster = ActiveWorkbook.Sheets("MasterSheet")

For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = wsMaster.Name And ws.Visible Then
    '<your code here>
    End If
Next ws
    
End Sub