SOLVED

VBA copying specific columns in new sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2404923%22%20slang%3D%22en-US%22%3EVBA%20copying%20specific%20columns%20in%20new%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2404923%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20need%20some%20help.%20I%20am%20trying%20to%20create%20an%20easy%20code%20on%20how%20to%20copy%20a%20specific%20column%20from%20one%20sheet%20to%20the%20other%20if%20a%20condition%20is%20meet.%20If%20column%20F%20%3D%22Yes%22%20in%20sheet%20%22request%20then%20I%20want%20column%20B%2C%20E%2CG%20copied%20into%20the%20next%20Sheet%20%22Sheet1%22.%20I%20don't%20want%20to%20specify%20how%20many%20rows%20will%20I%20have%20because%20it%20can%20change.%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3EThat's%20what%20i%20tried%20but%20not%20copying%20all%20columns%20so%20far.%3C%2FP%3E%3CP%3Elink%20for%20my%20spreadsheet%3A%3CA%20href%3D%22https%3A%2F%2Fonedrive.live.com%2Fedit.aspx%3Faction%3Deditnew%26amp%3Bresid%3DC051D6F258D8A704!124%26amp%3Bithint%3Dfile%252cxlsx%26amp%3Baction%3Deditnew%26amp%3BwdNewAndOpenCt%3D1622564472009%26amp%3BwdPreviousSession%3Db973bfc4-b1b5-41be-a7e7-b0b5fd6e15cc%26amp%3BwdOrigin%3DOFFICECOM-WEB.START.NEW%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fonedrive.live.com%2Fedit.aspx%3Faction%3Deditnew%26amp%3Bresid%3DC051D6F258D8A704!124%26amp%3Bithint%3Dfile%252cxlsx%26amp%3Baction%3Deditnew%26amp%3BwdNewAndOpenCt%3D1622564472009%26amp%3BwdPreviousSession%3Db973bfc4-b1b5-41be-a7e7-b0b5fd6e15cc%26amp%3BwdOrigin%3DOFFICECOM-WEB.START.NEW%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20code%3A%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton1_Click()%3C%2FP%3E%3CP%3Ea%3Dworksheets(%22Request%22).Cells(Rows.Count%2C1)End(xlUp).Row%3C%2FP%3E%3CP%3EFor%20i%3D2%20To%20a%3C%2FP%3E%3CP%3EWorksheets(%22Request%22).%20Activate%3C%2FP%3E%3CP%3EIf%20Worksheets(%22Request%22).Cells(i%2C6).Value%3D%22Yes%22%20Then%3C%2FP%3E%3CP%3ERange(Cells(i%2C1)%2C%20Cells(i%2C6)).Select%3C%2FP%3E%3CP%3ESelection.Copy%3C%2FP%3E%3CP%3EWorksheets(%22Sheet1%22).Activate%3C%2FP%3E%3CP%3Eb%3DWorksheets(%22Sheets1%22).Cells(Rows.Count%2C1).End(xlUp).Row%3C%2FP%3E%3CP%3EWorksheets(Sheets1%22).Select%3C%2FP%3E%3CP%3EActivateSheet.Paste%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3ENext%3C%2FP%3E%3CP%3EApplication.CutCopyMode%3DFalse%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-01%20at%2017.40.35.png%22%20style%3D%22width%3A%20695px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285375i28EEB99895D49013%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-01%20at%2017.40.35.png%22%20alt%3D%22Screenshot%202021-06-01%20at%2017.40.35.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-01%20at%2017.40.42.png%22%20style%3D%22width%3A%20333px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285376iA07E1D440CA2ED98%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-01%20at%2017.40.42.png%22%20alt%3D%22Screenshot%202021-06-01%20at%2017.40.42.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2404923%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2405119%22%20slang%3D%22de-DE%22%3ESubject%3A%20VBA%20copying%20specific%20columns%20in%20new%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2405119%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053100%22%20target%3D%22_blank%22%3E%40infinity94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIf%20you%20don't%20necessarily%20need%20VBA%2C%20here%20is%20a%20small%20example%20with%20the%20formula%20%22IF%22.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20hope%20that%20I%20could%20help%20you%20in%20your%20project.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ENikolino%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20know%20that%20I%20know%20nothing%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2405178%22%20slang%3D%22en-US%22%3EBetreff%3A%20VBA%20copying%20specific%20columns%20in%20new%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2405178%22%20slang%3D%22en-US%22%3Ethank%20you%20for%20this%2C%20but%20this%20leaves%20a%20lot%20of%20blank%20rows%20which%20I%20want%20to%20avoid.%20This%20is%20just%20a%20sample%20but%20when%20you%20have%202000%20rows%20this%20wouldn't%20be%20looking%20great%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

Screenshot 2021-06-01 at 17.40.35.pngScreenshot 2021-06-01 at 17.40.42.png

5 Replies

@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)

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
best response confirmed by allyreckerman (Microsoft)
Solution

@infinity94 

Here a quick mixed solution :))

With a hide macro

 

 

Nikolino

thank you!
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.