Identify, Copy, Paste

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
Hello All,
My first post! I am working on this scheduling project for work since everyone seems to enter parts into the system when ever they want. The employees use their own spread sheets wich complicates things more. With that said I'm sure I will have many questions on how to control all aspects of scheduling with visual basic locked Excel and Access databases. My goal is to start this in Excel and transition the work over to Access as I have done in many of my projects. The desired result is to know when a job should be at a machine and when it should be completed by planning and scheduling correctly.

Now for the question:
I'm looking for a vb code that will search a column and pull out the info out of the cells in the column and place it in a cell on a different worksheet. For example this is what I would have in a cell in D2 LZS,PBR,CHP,PRB. So, first the column D is identified, then the cells in the column are searched and if a cell contians LZS then LZS,PBR,CHP,PRB is copied and pasted in sheet LZS, sheet PRB, Sheet CHP, and sheet PRB.

The second issue I am having is that the 4 cells to the right of the column need to be selected as well and pasted in the appropriate sheets. This issue has bugged me for sometime. I look foward to the responses.

Is there a way I can post the excel sheet for download so everyone can see what has been layed out and what needs to be cleared up!? I have good code and forms in the project and as this project continues on I think it might serve as a good example for others looking to control sheduling issues.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Welcome to the Forum,

There will be a number of options you could use to achieve your goal.

First you could look to use filters and copy the range based on selections, then you could automate this via recording a macro. Here is a sample to get you going.

Sub mcrFilter()
' Apply a Filter to find data
Range("B2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$D$18").AutoFilter Field:=2, Criteria1:="Engineer"
End Sub
 
Upvote 0
Hi There,

Any Assistance would be great.

Beginner at work!

Using Excel 2007 in XP.

I need to creat a macro that will copy by draging down the above cells. but the above cells are different in each sequence. I have a macro to add in 3 blank rows every 8th row but i need to drag down the above rows to auto fill the black rows.
This is what i have so far.

PHP:
Sub InsertRows()
'insert 3 rows after every exiting 7th row
Dim lEndRow As Long
Dim lInstRow As Long
lInstRow = 8
Do
lEndRow = Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
Rows(lInstRow).Resize(3, 256).Insert
lInstRow = lInstRow + 10
Loop Until lInstRow > lEndRow
End Sub

Hope this makes sense.

Many thanks
 
Upvote 0

Forum statistics

Threads
1,224,518
Messages
6,179,245
Members
452,900
Latest member
LisaGo

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