Move certain data from one sheet to another

Status
Not open for further replies.

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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
Thanks for this. Whilst running the script I'm getting a run-time error (1004)

AutoFilter method of range class failed.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top