Move certain data from one sheet to another

[asherzt]

My experience in Macros/VBA is pretty limited but I am slowly trying to get my head around it.
I need some help finding a solution or code to help automate a part of a spread sheet which I will try and explain and hopefully someone can explain it to me.

I have a excel workbook with two sheets. i'LL just refer to them sheet one and two.

On Sheet one the headings for the cells start on A7 and move right the way across to AV7, with data in the rows underneath them. In any given month their could be loads of data or a small amount so it's always varying.
What i am looking to do is have excel move certain data across to sheet two if a certain value is entered.
So on sheet one, if 'Yes' is selected in any of the rows below M7 id' like it to move it to sheet two and remove it from sheet one and for this to happen to all the records which have 'Yes' entered into them.
When moving from sheet one to two however, i only want the certain information to move across and not the whole row.
The information to be moved is everything from C:E, I, AD:AL but not the headers just any of the data underneath with 'Yes' in column M
Hope this makes sense and i've explained it well enough. Thanks in advance for your help.

 

[vcoolio]

Hello Asherzt,

Try the following code:-

Sub Test()

Application.ScreenUpdating = False

With Sheet1.Range("A7", Sheet1.Range("AV" & Sheet1.Rows.Count).End(xlUp))
        .AutoFilter 13, "Yes"
        Union(.Columns("C:E"), .Columns("I"), .Columns("AD:AL")).Offset(1).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
        .AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code will filter Sheet1 Column M for the criteria "Yes" and then copy/paste the required thirteen non-contiguous columns of data to Sheet2 starting in Column A.

I hope that this helps.
Cheerio,
vcoolio.

 

[asherzt]

Thanks for this. Whilst running the script I'm getting a run-time error (1004)

AutoFilter method of range class failed.

 

[vcoolio]

Hello Asherzt,

I've just tested this on a sample of what I perceive your workbook to look like, i.e. based on your opening post, and I do not receive any errors. The code works as it should.

Upload a sample of your workbook to a free file sharing site such as Drop Box then post the link to your file back here. Please ensure that the sample is an exact replica of your workbook and if your data is sensitive then please use dummy data.

Cheerio,
vcoolio.

 

[rickert91]

I have the same thing. I have columns A-H Row 3, I only want certain cells B, C, D AND G to go to the next sheet. It is for ordering purposes

 

[vcoolio]

Hello Rickert,

Are your headings in Row2 with data starting in Row3? Do you have a particular criterion such as the OP's "Yes" and if so, which column is the criterion to be placed?

Cheerio,
vcoolio.

 

[rickert91]

Can I give you the onedrive link and have you take a look at it?

 

[kevin9999]

Can I give you the onedrive link and have you take a look at it?

Did you try out the code suggested in your other post?

 

[Fluff]

Duplicate to: Workbook help

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.