Hello there,
I'm trying to adapt some code that i found in another thread.
Sub MM2()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveSheet.Rows("1:" & lr)
.AutoFilter
.AutoFilter Field:=5, Criteria1:="Yes", Operator:=xlAnd
.SpecialCells(xlCellTypeVisible).EntireRow.Cut Destination:=Sheets("Sheet2").Range("A" & Sheets("Sheet2").UsedRange.Rows.Count + 1)
.Autofilter
End With
End Sub
I'm very new to VBA and have gotten a General knowledge of this stuff over the last weeks but i'm definitely missing a fair amount of what you'd consider Basic Knowledge. As such the above code may not actually be what i need.
What i'm trying to accomplish:
The workbook that i'm creating will be used to track orders being made offsite and being fulfilled by our home office.
It has 9 sheets.
Instructions, Dept1, Dept2, Dept3, Dept4, Received Dept1, Received Dept2, Received Dept3, Received Dept4
The 1st is just instructions / examples of the layout of the preceding sheets. It will be locked from accepting User changes
The all the sheets are laid out with the top 5 rows being information for the users.
The 6th row is a header row for a table, 7th row starts data entry. "Date Requested" is in column A
[TABLE="width: 1441"]
<tbody>[TR]
[TD="class: xl70, width: 119"]Date Requested[/TD]
[TD="class: xl71, width: 86"]Quantity[/TD]
[TD="class: xl67, width: 154"]Priority[/TD]
[TD="class: xl67, width: 140"]Order Status[/TD]
[TD="class: xl67, width: 123"]Lead Time / Expected city[/TD]
[TD="class: xl72, width: 555"]Detailed Description of item[/TD]
[TD="class: xl73, width: 264"]Other Notes[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1441"]
<tbody>[TR]
[TD="class: xl70, width: 119"]4/10/2019[/TD]
[TD="class: xl71, width: 86"]5[/TD]
[TD="class: xl71, width: 154"]ASAP[/TD]
[TD="class: xl71, width: 140"]Received[/TD]
[TD="class: xl71, width: 123"]2 weeks[/TD]
[TD="class: xl72, width: 555"]widgets[/TD]
[TD="class: xl73, width: 264"]Notes[/TD]
[/TR]
</tbody>[/TABLE]
Order Status has data validation with 4 option in a drop down list.
My goal is for there to be a button that moves a row that is marked as "Received" in a "Dept*" sheet to the corresponding "Received Dept*" sheet.
I don't know if it's necessary, but i might need a another button on the "Received Dept*" sheets to undo a move that was done in error.
When a "Dept*" sheet has a row removed from it i would like for the table to stay the same length,
I believe this would be done by Copying then Clearing a row vs. cutting. Correct me if i'm wrong.
When a "Received Dept*" sheet has a row transferred to it I would like the row to be inserted at the top of the table.
I also don't know if this would be easier with with one global macro and buttons pointing to it or 4 separate macros tied to their respective pairs of sheets.
I've tried to be as complete as I can with this post but i'm sure I've forgotten something.
Thank you all for any help or advice you can give!
I'm trying to adapt some code that i found in another thread.
Sub MM2()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveSheet.Rows("1:" & lr)
.AutoFilter
.AutoFilter Field:=5, Criteria1:="Yes", Operator:=xlAnd
.SpecialCells(xlCellTypeVisible).EntireRow.Cut Destination:=Sheets("Sheet2").Range("A" & Sheets("Sheet2").UsedRange.Rows.Count + 1)
.Autofilter
End With
End Sub
I'm very new to VBA and have gotten a General knowledge of this stuff over the last weeks but i'm definitely missing a fair amount of what you'd consider Basic Knowledge. As such the above code may not actually be what i need.
What i'm trying to accomplish:
The workbook that i'm creating will be used to track orders being made offsite and being fulfilled by our home office.
It has 9 sheets.
Instructions, Dept1, Dept2, Dept3, Dept4, Received Dept1, Received Dept2, Received Dept3, Received Dept4
The 1st is just instructions / examples of the layout of the preceding sheets. It will be locked from accepting User changes
The all the sheets are laid out with the top 5 rows being information for the users.
The 6th row is a header row for a table, 7th row starts data entry. "Date Requested" is in column A
[TABLE="width: 1441"]
<tbody>[TR]
[TD="class: xl70, width: 119"]Date Requested[/TD]
[TD="class: xl71, width: 86"]Quantity[/TD]
[TD="class: xl67, width: 154"]Priority[/TD]
[TD="class: xl67, width: 140"]Order Status[/TD]
[TD="class: xl67, width: 123"]Lead Time / Expected city[/TD]
[TD="class: xl72, width: 555"]Detailed Description of item[/TD]
[TD="class: xl73, width: 264"]Other Notes[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1441"]
<tbody>[TR]
[TD="class: xl70, width: 119"]4/10/2019[/TD]
[TD="class: xl71, width: 86"]5[/TD]
[TD="class: xl71, width: 154"]ASAP[/TD]
[TD="class: xl71, width: 140"]Received[/TD]
[TD="class: xl71, width: 123"]2 weeks[/TD]
[TD="class: xl72, width: 555"]widgets[/TD]
[TD="class: xl73, width: 264"]Notes[/TD]
[/TR]
</tbody>[/TABLE]
Order Status has data validation with 4 option in a drop down list.
My goal is for there to be a button that moves a row that is marked as "Received" in a "Dept*" sheet to the corresponding "Received Dept*" sheet.
I don't know if it's necessary, but i might need a another button on the "Received Dept*" sheets to undo a move that was done in error.
When a "Dept*" sheet has a row removed from it i would like for the table to stay the same length,
I believe this would be done by Copying then Clearing a row vs. cutting. Correct me if i'm wrong.
When a "Received Dept*" sheet has a row transferred to it I would like the row to be inserted at the top of the table.
I also don't know if this would be easier with with one global macro and buttons pointing to it or 4 separate macros tied to their respective pairs of sheets.
I've tried to be as complete as I can with this post but i'm sure I've forgotten something.
Thank you all for any help or advice you can give!