problem with selecting next row code

[smaier69]

Greetings, all.

I'm new to VBA, but the logic of coding in general is not lost on me. My problems usually stem from unfamiliar syntax.

Scenario... I have 2 worksheets (Sheet4 and Sheet5) in the same workbook. I am entering data into Sheet4 (either one line per macro run or multiple lines per.... doesn't matter) starting at row 4, as the above rows have headers and so forth. The macro looks to see if there are the initials "LP" in column "Q" (x, 17). If there is, it cuts that row and activates Sheet5 and pastes the cut row's data into the row and goes back to Sheet4 and loops until there are no more rows with "LP" in column Q.

The breakdown is that it keeps overwriting on the same row in Sheet5. Even if I save the workbook after each macro run, it doesn't offset like I think it's supposed to.

I'll be honest and admit this is not code I came up with entirely. Reading around I have sniped bits and pieces from all over, so if it looks like a VBA trainwreck I apologise. The worksheets also have colored cells which, from what I've read, can throw off the percieved bottom edge of the used rows. So I tried creating a new workibook (no formatting at all) and manually entered data in the necessary fields to make it cut/paste but I get an error (424 object required, iirc). I think the problem, amongst others, is I have fiddled with and tweakd with it too much in order to break.fix my way to learning.

Any guidance would be very much appreciated.

Please Login or Register  to view this content.

[xLJer]

Try this revised code. You do not have to move back and forth
between worksheets by activating them to do this cut-and-copy task in VBA.

Where you see no worksheet reference before ".Cells" and ".Rows"
in the code, Sheet4 is being referenced through the worksheet object ws4.

Please Login or Register  to view this content.

[smaier69]

That worked beautifully! A much more elegant approach than my caveman attempt.

A question if you don't mind. What is the function of the first two lines (Dim nextRow As Long, x As Long
Dim ws4 As Worksheet)? Though some of the code I have not tried to use due to me being green, stepping in and F8'ing it makes it perfectly clear. Understanding the Dim commands' impact on the rest of the code would prove useful for future attempts at VBA.

Next step for me is to learn the sytax necessary to control what elements within a cell get moved, but I'll save that for later reading.


I sincerely thank you for your help!

[xLJer]

Three variables are being declared at the top of the code.
Dim nextRow as Long is telling the system that a very large whole
number can be assigned to variable nextRow. The data type Long
was used here (instead of data type Integer) because, in Excel, there might
be the possibility that data rows exceed the number that a variable of data
type Integer can hold: 32,767.

The same explanation applies to the variable x.

The use of Dim ws4 as Worksheet in this case was just being elegant. I could have
not used it and instead typed in Sheets("Sheet4") everywhere it was needed, but I skipped
that by enclosing the section of code in With ws4 and End With
In my opinion, this makes the code a little less cluttered.

There may be more to using the Worksheet object in VBA code, but that is for
a future learning experience.