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

SouthernGent0327

New Member
Joined
Jan 30, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,200
Office Version
  1. 2016
Platform
  1. Windows
I have no idea how this code relates to the ComboBox you talked about. I think your code should be

VBA Code:
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

New Member
Joined
Jan 30, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I have no idea how this code relates to the ComboBox you talked about. I think your code should be

VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,200
Office Version
  1. 2016
Platform
  1. Windows
If you are trying to write in all worksheet, then why not loop through all sheets in the workbook?
VBA Code:
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
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,333
Messages
5,641,550
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top