• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy from one sheet to another based on cell value

D

DPD

New Member
Im looking to copy the first 4 rows of data from sheet Phase I and paste to sheet Phase II when column J is a YES. Currently the macro (YesI) works when I press the button once, if the button is pressed a second time it is copying and pasting data from Phase I sheet which does no have a YES on column J, also the macro is currently pasting the first 6 rows.
Second part is to get the data from sheets Group I and Group II and paste to the budget sheet, the issue is I dont know how many individuals will be on Group I or Group II.
Any help would be greatly appreciated
 

Attachments

  • ESSI Merit Exercise_Budget Blank test.xlsb
    165.4 KB · Views: 7
Hi,

The For loop in your macro Yes1 is running from row no. 2 to lastrow, each time it is checking if you have YES in column J, it will paste the values from first sheet to second sheet. So, a total of 5 rows getting copied. If you press it twice the same data is getting copied.

Do you want only the first four rows with YES in column J to be copied? so if column J has data like:
Yes
Yes
No
Yes
No
Yes
No
Yes

So row no 1,2,4,6 should get copy from top?

Regards,
Somendra Misra
 
I see. I would like all rows which have a Yes on column J to be copied and pasted to sheet Phase II so would be 5 rows of data on the sheet attached, I just dont want it to repeat the same data if the button is pressed again in case new data is added later. Thank you.
 
So the new data will be a continuous addition below the old data and every time the button is pressed it should copy new data?

Regards,
 
Correct, There is a chance new data will be added or old data corrected if a mistake is made, just want to be ready if that happens.
 
DPD said:
Correct, There is a chance new data will be added or old data corrected if a mistake is made, just want to be ready if that happens.
Click to expand...

DPD,

Correcting old data is actually what DATABASE are meant for. I would suggest you to paste everytime all the data in second sheet with YES on first sheet.
This will take care for any new entry or changed old entry.

What you say?

Regards,
 
I ve tried that but didnt work, copying and pasting all data with a yes every time would be perfect.
 
Try Below code:

Code: 
Sub Yes1()
'Copy from one sheet to another based on cell value
Application.ScreenUpdating = False
Dim i As Integer
Dim lastRow As Long
Dim LastroWB As Long
lastRow = Cells(Rows.Count, "J").End(xlUp).Row + 1
LastroWB = 5
For i = 5 To lastRow

If Cells(i, 10).Value = "YES" Then
Range(Cells(i, 1), Cells(i, 6)).Copy Sheets("Phase II").Range("A" & LastroWB)
LastroWB = LastroWB + 1
End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Regards,
 
It works the first time you press the button after than, it wont. Press it once, than change something on Phase I and press again. Seems to work only the first time the button is pressed.
 
Try Below code:

Code: 
Sub Yes1()
'Copy from one sheet to another based on cell value
Application.ScreenUpdating = False
Dim i As Integer
Dim lastRow As Long
Dim LastroWB As Long
lastRow = Cells(Rows.Count, "J").End(xlUp).Row + 1
LastroWB = 5
Sheets("Phase II").Range("A5:F" & Sheets("Phase II").Cells(Rows.Count, 1).End(xlUp).Row).ClearContents

For i = 5 To lastRow

If Cells(i, 10).Value = "YES" Then
Range(Cells(i, 1), Cells(i, 6)).Copy Sheets("Phase II").Range("A" & LastroWB)
LastroWB = LastroWB + 1
End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Regards,
 
You must log in or register to reply here.
Back
Top