Move certain data from one sheet to another

asherzt

New Member
Joined
Sep 13, 2019
Messages
2
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

Well-known Member
Joined
Jun 29, 2014
Messages
853
Hello Asherzt,

Try the following code:-


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

New Member
Joined
Sep 13, 2019
Messages
2
Thanks for this. Whilst running the script I'm getting a run-time error (1004)

AutoFilter method of range class failed.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
853
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.
 

Forum statistics

Threads
1,085,311
Messages
5,382,897
Members
401,808
Latest member
huyennhiteen9xx

Some videos you may like

This Week's Hot Topics

Top