Trying to copy paste and delete a row

So I am working on a project that is a spreadsheet that tracks orders. When the order is in house and received and the drop down shows received the code is supposed to copy that row and move it to Sheet2 and paste it into the next available line and then delete it from the first sheet (SheetA) and then move all the information up to close the gaps. I have two bits of code that I have been trying to get to work but for some reason neither will do anything like I was hoping for. Here is the first bit of code:
[vba]Sub CopyPaste()
Dim MyRow As Integer
Dim MyCell As String
Dim LookCell As String
Dim TargetRow As Integer
Dim TargetRange As String
'Find first open row on sheet 2
TargetRow = 2
Do While Sheet2.Range("A" & CStr(TargetRow)).Value <> Empty
TargetRow = TargetRow + 1
Loop
TargetRange = "A" & CStr(TargetRow) & ":L" & CStr(TargetRow)
'Search Sheet 1 for L column = "4"
MyRow = 2
MyCell = "A" & CStr(MyRow)
LookCell = "L" & CStr(MyRow)
Do While Sheet2.Range(MyCell).Value <> Empty
If SheetA.Range(LookCell).Value = "4" Then
Sheet2.Range(TargetRange).Value = SheetA.Range(MyCell & ":L" & CStr(MyRow)).Value
SheetA.Range(MyCell).EntireRow.Delete (xlShiftUp)
TargetRow = TargetRow + 1
TargetRange = "A" & CStr(TargetRow) & ":L" & CStr(TargetRow)
Else
MyRow = MyRow + 1
End If
MyCell = "A" & CStr(MyRow)
LookCell = "L" & CStr(MyRow)
Loop
End Sub
[/vba]
And here is the second bit. The theory behind the second bit is to work from the bottom of the list up but I cant seem to get it to work either since I am really not a VBA programmer and am very new at this.
[vba]For I = Sheet2.Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
MyCell = "A" & I
LookCell = "L" & I
If SheetA.Range(LookCell).Value = "4" Then
Sheet2.Range(TargetRange).Value = SheetA.Range(MyCell & ":L" & CStr(MyRow)).Value
SheetA.Range(MyCell).EntireRow.Delete (xlShiftUp)
TargetRow = TargetRow + 1
TargetRange = "A" & CStr(TargetRow) & ":L" & CStr(TargetRow)
End If
Next I[/vba]
I also have the worksheet on my drop box as well. Here is the link. Any help or suggestions would be greatly appreciated!

The Empty Keyword only applies to variables:[VBA]Dim X
'X is now Empty
X = 2
'X is no longer Empty
Range("A1") = Empty ' Invalid use of Empty[/VBA]

Instead use: [VBA].Value <>""[/VBA]

OKay Thank you I will give that a shot!

Okay I tried that and it doesnt stop for errors now but it also does not remove the line with a status of "4" either. I mist still be missing something

I will keep messing with this and see what I come up with though.

I will try to send a link to my dropbox in a second when it allows me to.

https://www.dropbox.com/s/oxhytt0jv6...ker%282%29.xls
This is where you can access the excel file I have been working on if you think that will help. Also I am working with excel 2010 if that changes anything.

To upload attachments, click on Go Advanced, scroll down to Manage Attachments and follow the prompts from there

Ted,

He can't. 1.6MB file size. He has at least 4000 Excel4 Form controls on one sheet.:rofl:

At least 1000 of those are hidden behind others.

Well I'm sure he could if the file was reduced in size by removing at least 950 rows (or more) of controls.

Archangel,

I have carefully looked over your workbook, and I hate to tell you, but it will take lots of cludgy code to make it work, and the code will have to be completely rewritten if you ever make any changes to the book.

I strongly suggest that you consider using a UserForrm for the data entry and record editing.

I am attaching an example of what the shhets in your UserForm driven App would look like and an example of a UserForm driven App I am developing for someone else.

Note the the example App has 6 worksheets, each with as much data as your uploaded example, 50 times the code of yours, must lookup sheets and ranges based on values shown in the form and has a 400KB image imbedded in it, yet is still only half the size of your book.

Try it out, just ignore any error messages. It still loads and saves records, validates inputs and fills in data based on prior data entry fields.

If you still feel that your way is the way to go, let us know and we will get it working for you. But please, remove 3,970 of those checkboxes and listboxes before you upload another example. Oh, you probably haven't noticed, but each listbox is exactly over another hidden listbox, so you have twice as many as you see.

Archangel,

The UserForm Driven App I mentioned

Okay thats kinda what I was wondering. Thank you for all your help. Also I didnt realize that there were all the hidden controls on that sheet like that. Again thank you!

Thank you to all that have helped and since reading all the helpful posts on how to do this, my work partner and I have since changed the way we are doing this to a form based system. I am working on the script and forms at the moment and I have some of the forms built already. I may need some direction on a few things as we go along so I will keep this thread going on this subject. Again thanks for all the direction!

Okay so I have some code that I have been messing with and it will enter "test" into the sheet but now I need to link it to UserForm1 corresponding entry blanks to the columns on the sheet. Here is the code and I will link the workbook in a few minutes.
[VBA]Private Sub Test()

Dim lngWriteRow As Long

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

lngWriteRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

If lngWriteRow < 2 Then lngWriteRow = 2
ws.Range("A" & lngWriteRow) = "test"
ws.Range("B" & lngWriteRow) = "test"
ws.Range("C" & lngWriteRow) = "test"
ws.Range("D" & lngWriteRow) = "test"
ws.Range("E" & lngWriteRow) = "test"
ws.Range("F" & lngWriteRow) = "test"
ws.Range("G" & lngWriteRow) = "test"
ws.Range("H" & lngWriteRow) = "test"
ws.Range("I" & lngWriteRow) = "test"
ws.Range("J" & lngWriteRow) = "test"
ws.Range("K" & lngWriteRow) = "test"
End Sub
[/VBA]
Thanks again! Please remember that this is a work in progress and there are a few things in here that are being repurposed from a different project that we had completed successfully. Again Thank you!

Here is the link to my drop box where the file is located. Thanks Again!
https://www.dropbox.com/s/2j8pobkawn...ype%281%29.xls

Not a bad First try.

Now Johnny, you've been a bad boy, I want you to write, on the blackboard, 100 times,

Quote:

---

Function Follows Data Structure
All Names are the Same. Except Prefixes
Forms follow Work Flow

---

:bug: :rotlaugh:

Seriously now:

Function Follows Data Structure
You don't know what to do or how to do it until you have your Data Structured.

Data must be structured in a neat, concise, unduplicated way. The Structure can and should be as identical as possible when one Table (Sheet) is used for Records (Rows of Data) with different statuses than another. See sheets "Outstanding POs" and "Completed POs" in the Archangel attachment in my post above. Get those two arranged and formatted the way you prefer/need and fill in some dummy data on "Outstanding POs" with several statuses. First see next para, this post.

The Sheet "vbaLists" should hold lists of every possible value for the value types shown in the header row. These lists will be used to populate Drop Downs in the Forms, so Users cannot enter wrong values. Also add any columns you think you may want as validations or dropdowns.

All Names are the Same
After you've designed the Data Storage sheets and the Lists sheet, make a list of all the Unique header names from all three sheets.

Using this list make some Range names that are very similar to the sheet header names, except that all spaces are removed or replaced with underscores. You can use common abbreviations and memnomics such as PrtNo for "Part Number" and PrtDesc for "Part Description," "MfgPrtDesc," etc.

Now choose prefixes that represent each sheet name. For example "open," comp," and "list" or "vba_." No Caps Please. Note that all sheet prefixes should have the same number of characters.

Using the Sheet appropriate prefix create defined names for each column on each sheet. Example openPrtNo, vba_PrtNo, and compPrtNo. Note pattern of CapLettersInNames.

UserForms generally consist of Label/input box pairs. The labels usually don't need to be named, but the input box names should be identical to the Range names without the prefix. Input boxes can be drop down ListBoxes or type-in TextBoxes. You can use any vbaLists Validation lists to validate TextBox entries and will use vbaLists dropdown lists to fill the ListBoxes. the ListBox code might look like this, where lbx is a standard prefix for a ListBox name:[vba]lbxPrtNo.RowSource = vbaLists.Range("listPrtNo")[/vba] and the code to record that part number might be[vba]Sheets("Completed POs").Range("compPrtNo").Cells(NextRow) = lbxPrtNo[/vba]
See the pattern? All Names are the Same. Except Prefixes. Make all Form prefixes the same number of characters. Makes coding a breeze. Just by looking at the input box name, you know exactly where to get, and where to put, its' data. If you study the code in the UserForm App attachment I gave you you will find examples of how to load and store the entire form with one loop.

Forms follow Work Flow
If all PO numbers are unique, when updating a record, you can have the Form itself fill in most input boxes by merely finding the row of an existing PO. Make the PO number input box the first one on the form. If its a new PO number, well its not that hard on the user compared to the value an auto fill gives. About a third of the time the only changes the User makes is to update the Status, maybe that box should be next. OR, what is the first item on the (paper?) form the user is looking at to complete the UserForm? Maybe that should be the next input box, etc.

Creating the Form
Looking at all the example available it looks like the only input boxes that cannot be ListBoxes are quantities , dates, and the PO number itself.

To a blank Form Add one Label and one TextBox. Align them, make them appear to be the same height, set their properties. Add no text or names at this time. Use the Ctrl key and the mouse to select both of them. Right Click and drag them to just below where they are and select Copy Here. Repeat until you have a pair for each needed TextBox type entry.

Drag the mouse (left button) over all the labels and Text Boxes, Left grab a cross hair handle and drag them over out of the way.

Repeat the above for the number of ListBox types you need.

Remember that Forms Follow Workflow. Grab a TextBox/Label pair and drag them to the top of the Form, about the center area or exactly where you think they'll go. Type in the Labels text, "P.O. Number", and expand it to the left to show all the text. Name the inputbox "txbPONum" or whatever, and set its TabIndexNumber to 0. When the Form starts, TabIndex 0 will be selected for input.

What is the next input in the workflow? Grab that pair, move them as desired, name the box and set its TabIndex to 1. When the User presses Tab or Enter while in the tbxPONum box (TabIndex 0), the box with TabIndex = 1 will be selected for input.
Repeat for all Label/Input Box pairs. If you rearrange the input boxes, select them by Work flow and renumber their TabIndexes.

Coding the Form
You will need to refer to the App attachment I posted in a previous post.

Copy all the declarations, (What is above the first sub,) in the Form code in the App to your Form code.

Right click on the Form and select View Code. At the top of the Code Pane. in the box labeld "General,"and in reverse Work Flow, select a lbx* control. VBA will automatically insert a Click Event Sub for that control. In the right hand box at the top of the code pane, select "enter" and it will insert an Enter event sub for the control. Delete the Click event sub and select the reverse Work Flow's next listbox, and repeat. When done you should have a series of ListBox Enter subs in WorkFlow order.

Click on UserForm in the "General" box and insert the Initialize sub. Paste
"InitializeControls
LoadLocalCollections" in it.

Find an Enter sub in the App example and paste that code line into all your Enter subs.

Paste this in[vba]Private Function LoadLocalCollections()
'Collections used for various Form activities

Dim Ctrl As Object
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "ListBox" Then ListBoxes.Add Ctrl
If TypeName(Ctrl) = "TextBox" Then TextBoxes.Add Ctrl
IntakeBoxes.Add Ctrl
Next Ctrl
End Function[/vba]
There are many more subs and functions in the App that you can paste into your code. Be sure to look at the workbook and worksheets code too.

See ya soon.

Okay so now with all of your help we have almost a complete working workbook. There is one last thing I cant seem to get working correctly and I added the code to my form so that it will not let information be submitted without all the textboxes being filled in. I know this will be something really simple but I havent gotten it yet. Here is the code:
[VBA]Private Sub CommandButton1_Click()

RowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Sheet1").Cells(RowCount, "A").Value = UserForm1.TextBox1.Value
Sheets("Sheet1").Cells(RowCount, "B").Value = UserForm1.TextBox2.Value
Sheets("Sheet1").Cells(RowCount, "C").Value = UserForm1.TextBox3.Value
Sheets("Sheet1").Cells(RowCount, "D").Value = UserForm1.TextBox4.Value
Sheets("Sheet1").Cells(RowCount, "E").Value = UserForm1.TextBox5.Value
Sheets("Sheet1").Cells(RowCount, "F").Value = UserForm1.TextBox6.Value
Sheets("Sheet1").Cells(RowCount, "G").Value = UserForm1.TextBox7.Value
Sheets("Sheet1").Cells(RowCount, "H").Value = UserForm1.TextBox10.Value
Sheets("Sheet1").Cells(RowCount, "I").Value = UserForm1.TextBox8.Value
Sheets("Sheet1").Cells(RowCount, "J").Value = UserForm1.TextBox9.Value

If Me.TextBox1.Text = "" Then
If Me.TextBox2.Text = "" Then
If Me.TextBox3.Text = "" Then
If Me.TextBox4.Text = "" Then
If Me.TextBox5.Text = "" Then
If Me.TextBox6.Text = "" Then
If Me.TextBox7.Text = "" Then
If Me.TextBox8.Text = "" Then
If Me.TextBox9.Text = "" Then
If Me.TextBox10.Text = "" Then

MsgBox "Please enter data in ALL boxes please."





UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.TextBox5.Value = ""
UserForm1.TextBox6.Value = ""
UserForm1.TextBox7.Value = ""
UserForm1.TextBox8.Value = ""
UserForm1.TextBox9.Value = ""
UserForm1.TextBox10.Value = ""

[/VBA]
Again thank you all for your help and direction!

What I keep getting is a compile error because of the End If statement and the End Sub statement.

[VBA]Private Sub CommandButton1_Click()

'look at the Form Code in the App example I posted and find all instances
'of "LoadLocalCollections" and "textBoxes." Add them to your Form code

Dim Ctrl As Object 'Add to Declarations section of your form

'Check for completeness
For Each Ctrl In TextBoxes
If Ctrl = "" Then GoTo Incomplete 'Jump to below
Next Ctrl

'If we're here, must be complete
RowCount = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

With Sheets("Sheet1") 'Much faster, easier to code
.Cells(RowCount, "A").Value = UserForm1.TextBox1.Value
.Cells(RowCount, "B").Value = UserForm1.TextBox2.Value
.Cells(RowCount, "C").Value = UserForm1.TextBox3.Value
.Cells(RowCount, "D").Value = UserForm1.TextBox4.Value
.Cells(RowCount, "E").Value = UserForm1.TextBox5.Value
.Cells(RowCount, "F").Value = UserForm1.TextBox6.Value
.Cells(RowCount, "G").Value = UserForm1.TextBox7.Value
.Cells(RowCount, "H").Value = UserForm1.TextBox10.Value
.Cells(RowCount, "I").Value = UserForm1.TextBox8.Value
.Cells(RowCount, "J").Value = UserForm1.TextBox9.Value
End With

'Reset Textboxes
For Each Ctrl In TextBoxes
Ctrl = ""
Next Ctrl
Exit Sub

'Jumping to here
Incomplete:
MsgBox "Please enter data in ALL boxes please."
End Sub[/VBA]

I will give this a shot! Thank you tons!