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
Related:
Automatically copy cells from one sheet to another
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.
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.
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:
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.
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.
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