200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
hello
i try creating macro if in column g contain yes then copy values from column b and paste from b31 but it doesn't work well
Sub test1()
Dim rcnt As Long
rcnt = Worksheets("test").Range("g" & Rows.Count).End(xlUp).Row
For i = 1 To rcnt
If Range("G" & i).Value = "YES" Then
Range("G" & i).Offset(0, -5).Copy
Range("B30").Offset(1, 0).PasteSpecial xlPasteAll
End If
Next i
Application.CutCopyMode = False
End Subthanks in advance
Leopard, I ran through your code and made some small changes to it, you did a really good job and got almost all the way there but you needed to find the next empty row for where you pasted the data - I added that line of code and then changed how you reference the range to paste the data and, since I'm in a good mood, even tidied up the code a bit lol.
Here you go:
Sub test1()
Dim rcnt As Long, nextBRow As Long
rcnt = Worksheets("test").Range("g" & Rows.Count).End(xlUp).Row
For i = 1 To rcnt
If Range("G" & i).Value = "YES" Then
' Find the next empty row of data in the "paste region".
nextBRow = Range("B30").CurrentRegion.Rows(Range("B30").CurrentRegion.Rows.Count).Offset(1).Row
Range("G" & i).Offset(0, -5).Copy
Range("B" & nextBRow).PasteSpecial xlPasteAll
End If
Next i
Application.CutCopyMode = False
End SubThe funny thing is that this exact type of scenario is what I am finishing for a "Practical Tutorial" for the ranges section of the VBA course I'm making and you just so happened to post this question the same day I was finishing that specific tutorial haha.
Ranges can be Very confusing to deal with in VBA so do not feel bad at all with your code. If you have any questions with the changes I made, let me know and I'll post a longer explanation, but the basics of it are that Range("B30").CurrentRegion is basically the same as selecting cell B30 and hitting Ctrl + A on the keyboard and it is this functionality that makes it easier to find the next empty row for that section of the worksheet.
I hope this helps :)