Auto Copy Row from one Sheet to another via Info in first Cell Solved/Closed

Question

Hello everyone, hope you all had a great New Year.

I am in the process of exporting a BOM out of Inventor and then opening it with Excel. Once I have it in Excel I am using record macro to get a layout to be used for other departments to use this information. (Hiding columns, sorting, and formatting cells)  What I am trying to accomplish is to transfer certain rows to a second sheet and the remainder to a third sheet. This way the two different department just have to go to the corresponding tabs. In my first column I only have two possible values, Normal and Purchased. I would like to attempt to have all the Purchased value cells in the first column go to a tab called "Purchased" and thus the same for "Normal" to go to a tab called "Normal". But there is one catch, there could be a min. of two rows, with information in them, and an unknown max quality of rows. I am also wanting to have all the columns go with said tab and have the column width be the same. This would also need to have the ability to add columns if necessary. 

I have looked all over this forum and have found similar questions, but not quite getting me where I would like to be. Any kind of help will be gratefully accepted.

Thank you for taking the time to look over my inquiry and have a nice day and a Happy New Year!

Thanks.

Chris

TrowaD · Accepted Answer

Hi Chris,

Why don't you create a template for that. A workbook with 3 sheets: Master, Purchased and Normal. Apply all the formats (columns width, header, etc.) you want.

When you get your next BOM, paste it in the Master sheet and save the workbook under a different name. Then run the code below to copy/paste each row to it's according sheet (Only works for pre-created sheets):

Sub RunMe()
Sheets("Master").Select
For Each cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    cell.EntireRow.Copy Sheets(cell.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell
End Sub

Is this what you were looking for?

As a novice you might find this helpfull:
How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens or you want to go back to the situation before the code was run.

Best regards,
Trowa

TrowaD · Answer

Hi Chris,

I can follow your query up to the point where you mention the catch. 


"But there is one catch, there could be a min. of two rows, with information in them, and an unknown max quality of rows."

So there is a minimum of 2 rows and an unknown number of maximum rows. So you have a variable number of rows, is that the catch?

"I am also wanting to have all the columns go with said tab and have the column width be the same."

To do that copy your main sheet twice, remove data and rename to "Normal" and "Purchased".

"This would also need to have the ability to add columns if necessary."

Don't you always have the ability to add an extra column?

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

DK97008 · Answer

Trowa,

Thank you for the response. I can clarify what I am trying to say.

-"But there is one catch, there could be a min. of two rows, with information in them, and an unknown max quality of rows."

When I export the items from Inventor I can have multiple rows, meaning  the number of rows is unknown, until I do the export.

-"I am also wanting to have all the columns go with said tab and have the column width be the same." 

I meant to say that I want the column description (which will always be row one) to be included in the new sheets.

-"This would also need to have the ability to add columns if necessary."

This last one you can just ignore this one.

Please let me know if I need to clarify any more. I am just a novice when it comes to Excel.

Thanks again,

Chris

TrowaD · Answer

Hi Chris,

You are right on why it is not working. If there is no Sheet2 nor Sheet3, then there are no sheets to copy the data to. This would create an error but since you use "On Error Resume Next", the error is skipped.

You could create your sheets at the start of your code:
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Normal"
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Purchased"
Sheets("BOM").Select

Best regards,
Trowa

Auto Copy Row from one Sheet to another via Info in first Cell

4 responses

Similar discussions

Subscribe To Our Newsletter!