VBA copying specific columns in new sheet

[infinity94]

Hi all,

I need some help. I am trying to create an easy code on how to copy a specific column from one sheet to the other if a condition is meet. If column F ="Yes" in sheet "request then I want column B, E,G copied into the next Sheet "Sheet1". I don't want to specify how many rows will I have because it can change.

Can someone help me with this?

That's what i tried but not copying all columns so far.

link for my spreadsheet:https://onedrive.live.com/edit.aspx?action=editnew&resid=C051D6F258D8A704!124&ithint=file%2cxlsx&act...

my code:

Private Sub CommandButton1_Click()

a=worksheets("Request").Cells(Rows.Count,1)End(xlUp).Row

For i=2 To a

Worksheets("Request"). Activate

If Worksheets("Request").Cells(i,6).Value="Yes" Then

Range(Cells(i,1), Cells(i,6)).Select

Selection.Copy

Worksheets("Sheet1").Activate

b=Worksheets("Sheets1").Cells(Rows.Count,1).End(xlUp).Row

Worksheets(Sheets1").Select

ActivateSheet.Paste

End If

Next

Application.CutCopyMode=False

End Sub

[#2]

@infinity94 

If you don't necessarily need VBA, here is a small example with the formula "IF".

I hope that I could help you in your project.

Nikolino

I know that I know nothing (Socrates)

[#3]

thank you for this, but this leaves a lot of blank rows which I want to avoid. This is just a sample but when you have 2000 rows this wouldn't be looking great

[#4 · Accepted solution]

@infinity94 

Here a quick mixed solution :))

With a hide macro

Nikolino

[#5]

thank you!

[#6]

Your welcome

Hope you have come to a satisfactory solution.

Wish you a nice day / night with lots of health, joy and love.

Nikolino
I know I don't know anything (Socrates)

*If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.