Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
6
Registration date
Monday January 4, 2016
Status
Member
Last seen
January 22, 2016
 -
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 -
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

4 replies

Answer 1 / 4
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 457
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
4
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
6
Registration date
Monday January 4, 2016
Status
Member
Last seen
January 22, 2016
 1
Trowa,

Hope you had a good weekend. Sorry I have not gotten back to you on my issues. I was working on something else that was a bit more important. I will go ahead and copy the code that I have already into this to show you what I have so far.

Sub TEST_MACRO()
'
' TEST_MACRO Macro
'

'
Columns("A:A").Select
ActiveWorkbook.Worksheets("BOM").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BOM").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("BOM").Sort
.SetRange Range("A2:L242")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C:C,D:D,G:G").Select
Range("G1").Activate
Selection.EntireColumn.Hidden = True
Range("K19").Select
Columns("A:A").ColumnWidth = 14.86
Columns("F:F").ColumnWidth = 26.14
Columns("L:L").ColumnWidth = 14.29
Range("B:B,E:E,H:H,I:I,J:J,K:K,L:L").Select
Range("L1").Activate
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1:L1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B:B,E:E,H:H,I:I,J:J,K:K,L:L").Select
Range("L1").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2").Select

With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter 1, "Normal"
On Error Resume Next
.Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)
End With

.AutoFilterMode = False
End With
With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter 1, "Purchased"
On Error Resume Next
.Offset(1).EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
.AutoFilterMode = False
End With



End Sub

It all works up till the point of " Range("A2").Select". for some reason it will not actually copy the rows to the next sheet tabs. As I hit the F8 button while in the Visual Basic editor I can see that it is separating out the "Normal" and "Purchased" but it never actually copies anything over. At the same time it is not creating the additional tabs. I do notice that when I open the exported file in Excel that I only have one tab at the bottom labeled "BOM". Could this be my issue on why it does not copy over the information to the "Sheet1" and "Sheet2".

I will continue working on this until I get my needed look on it.

Thanks again for all your help.

Regards.

Chris
Answer 2 / 4
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 457
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.
Answer 3 / 4
Posts
6
Registration date
Monday January 4, 2016
Status
Member
Last seen
January 22, 2016
 1
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
Answer 4 / 4
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 457
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
Report
Posts
6
Registration date
Monday January 4, 2016
Status
Member
Last seen
January 22, 2016
 1
Hello TrowaD

Again thank you for all your help. I have added the above code to mine. And it acts as if it wants to it. It will filter the Normal and the Purchased. But it still is not coping the info to the other sheet tabs. It is as if there is some kind of break in it. Still at a loss here, but I will not give up on this. I know that this should work, it just seems there is still a missing piece to the puzzle.

Again thank for all your help, it is much appreciated.

If I can get this to work I owe you big time.

Regards,

Chris
Report
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 457
I still stand behind behind the idea from the Jan 14 post.

What don't you like about that idea?
Report
Posts
6
Registration date
Monday January 4, 2016
Status
Member
Last seen
January 22, 2016
 1
TrawaD,

Thank you again for all your help.

I really did not look at the Jan. 14 option to closely. And now I think I can possible make these work for me. I will work on this today, and will let you know how I far.

Again thanks for all your help. I wish there was a way of paying you back.

Regards.

Chris
Report
Posts
6
Registration date
Monday January 4, 2016
Status
Member
Last seen
January 22, 2016
 1
TrowaD,

I was able to work that into my macro and it works great!!! Thank you very much for all your help.

Now the next step is to compile all of them tabs into one workbook. So, I can produce a cut sheet for all my parts.

Wish me luck.

Regards.

Chris
Report
Posts
2697
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 23, 2021
 457
Good luck Chris!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!